Highly Cohesive

How to page in MySql

• MySql uses the LIMIT keyword
• Syntax:  LIMIT <first item>, <numberofrows>
• The first item is 0, not 1.
• EX:  SELECT * FROM user ORDER BY name WHERE name LIKE ‘j%’ LIMIT 10, 5
• Example returns the “third” page of a set paged as 5 items per page.

How to List Indexes in MySql

Tired of performance woes with MySql?  Before deciding that normalization is bad, try indexing your MySql tables.

• SHOW INDEX FROM mydb.mytable
• SHOW INDEX FROM mytable FROM mydb
• Statements are equivalent
• EX:  SHOW INDEX FROM sitedb.user

How to create an index in MySql

• CREATE INDEX indexname ON tablename(fieldname)
• EX:  CREATE INDEX idx_user_id ON user(id)

How to create multiple column index (composite index) in MySql

• CREATE INDEX indexname ON tablename(fieldname1, fieldname2)
• EX:  CREATE INDEX idx_user_id ON user(id, name)