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)

How To Login to MySql

• mysql -uuser -ppassoword
• Example mysql -uroot -pthisismypassword
• mysql –user=user –password=password
• Example mysql –user=root –password=thisismypassword

How to List databases in MySql

• at mysql command prompt> show databases;
• Ex:  mysql> show databases;

Select a database in MySql

• at mysql prompt> use databasename;
• Ex:  mysql> use my_db;

Select list of tables in MySql

• at mysql prompt> show tables;
• Ex:  mysql> show tables;

List columns in MySql table

• at mysql prompt> show columns from tablename;
• Ex: mysql> show columns from my_table;

Ever noticed how everything in software is clannish?   We must be near the top of industries driven by “trendy” production processes… right after organic farmers.