1. Use EXPLAIN keyword in your SELECT queries and then add index to all the search fields in the query:
ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);
//before using index
//after using index (not the dramatic decrease in the number of rows scanned)
2. If required create an index on a table column and choose the data structure to be used:
create index your_index_name on your_table_name(your_column_name) using HASH;
or
create index your_index_name on your_table_name(your_column_name) using BTREE;
3. Limit select when looking for a single row
// do I have any users from Alabama?
// what NOT to do:
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
if (mysql_num_rows($r) > 0) {
// ...
}
// much better:
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}
4. Optimize queries to utilize query cache
// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
5. Index and Use Same Column Types for Joins
// looking for companies in my state
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");
// both state columns should be indexed
// and they both should be the same type and character encoding
// or MySQL might do full table scans
6. Do Not ORDER BY RAND()
// what NOT to do:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// much better:
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
7. Avoid SELECT '*'
// not preferred
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// better:
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// the differences are more significant with bigger result sets
8. Almost Always Have an id Field having one of the flavors of INT
//Every table must have an id column that is the PRIMARY KEY, AUTO_INCREMENT and one of the flavors of INT. Also preferably UNSIGNED, since the value can not be negative.
//Even if you have a users table that has a unique username field, do not make that your primary key. VARCHAR fields as primary keys are slower.
//One possible exception to the rule are the "association tables", used for the many-to-many type of associations between 2 tables. //For example a "posts_tags" table that contains 2 columns: post_id, tag_id, that is used for the relations between two tables named "post" and "tags". These tables can have a PRIMARY key that contains both id fields.
9. Use PROCEDURE ANALYSE() to know the optimal data types for columns
//PROCEDURE ANALYSE() will let MySQL analyze the columns structures and the actual data in your table to come up with certain suggestions.
//Certain data types have definite performance advantages like using ENUM over VARCHAR when applicable (ENUM type columns give speed boost as ENUM type columns are very fast and compact) etc.
10. If possible always set your columns as NOT NULL
//NULL columns require additional space and they can add complexity to comparison statements.
11. Choose the Right Storage Engine
//MyISAM is good for read-heavy applications, but it doesn't scale very well when there are a lot of writes. Even if you are updating one field of one row, the whole table gets locked, and no other process can even read from it until that query is finished.
//MyISAM is very fast at calculating SELECT COUNT(*) types of queries.
//MyISAM supports full text search index.
//MyISAM is older and stagnant.
//InnoDB tends to be a more complicated storage engine and can be slower than MyISAM for most small applications. But it supports row-based locking, which scales better and has good resistance to table corruption.
//It also supports some more advanced features such as transactions, relationship constraints, foriegn keys and crash recovery.
//InnoDB does not support full text search index.
//InnoDB is a new engine and all further development/research is being done on InnoDB.