MySQL optimization Checklist

Server Setup

                                                                                                                                                      Complete

Root user is setup with a secure password, and local access only
Have a backup and restoration plan in place, in case database server is completely destroyed.
Install method or program to benchmark performance, and enable the slow query log.
Turn off MySQL's DNS service, and authenticate by IP only if possible.
Only grant access for specific "privileges ON db TO user @ host". Do not ever use: GRANT ALL ON EVERYTHING TO USER@ANYWHERE.

Schema Design

Complete

Name database, tables and column with lower case letters and an "_" separating words. `id` is not a descriptive name!
Database, tables, and columns are using the same collation and character set and not UTF‐8 unless required.
Foreign keys being used to maintain data integrity ‐ N/A for MyISAM
Tables segmented logically based on their purpose.
Don't use any reserved word (date, int, decimal, time, etc.) for a database, table or column names.

Table Design

Complete              

Use proper data types. For example, numbers and dates not stored in character or text fields.
Use unsigned int(10) instead of bigint for columns requiring large numerical keys.
Use the smallest length for the data type being used. No VARCHAR(255), BIGINT(20), DECIMAL(20,2), etc.
Avoid using TEXT and BLOB data types whenever possible.
Do not store data in a non‐relational format (value1,value2,value3, in a single column) . Use multiple tables and joins to retrieve one‐to‐many relationships and to preserve data integrity.

Index Optimization

Complete

Use the proper indexes (unique, normal, partial, etc.) for the data, and do not create duplicate indexes.
Don't modify an indexed field in a query or the index will not be used. " WHERE DATE(`date_of_birth`) > '2009‐07‐01' "
Use multi‐column (concatenated) indexes for columns that frequently get queried together.
Try to use indexes in columns with a high cardinality (many unique values in relation to the total rows) .
Avoid indexing columns with very few unique values or many null's. An empty value is better than NULL.

Query Optimization

                                                                                                                                                      Complete

Use specific column names when selecting. Avoid: SELECT *.
Use MySQL's built‐in functions (COUNT, SUM, AVG, etc.) instead of application level functions.
Do not select TEXT or BLOB columns unless absolutely necessary.
Use transactions whenever applicable.
Use SQL_NO_CACHE for very large queries, and queries on data that changes frequently

Bonus ‐ 5 Tips and Tricks

Complete

Use TIMESTAMP and not DATETIME for all date fields newer than Jan 01 1970.
Never use SIGNED INT fields unless you actually need to store negative numbers.
The _ci at the end of a collation means "case insensitive". Use _ca collation if you care about case sensitivity.
Know that InnoDB is often a better choice than MyISAM. Don't use MyISAM just because it is default and somebody told you its better.