MySQL Optimization Tutorial
A while back I wrote a little introductory MySQL Administration tutorial. The tutorial had your basic information about starting the database server and creating databases. Now, I would like to jot down some commands that are useful when trying to optimize a SQL query on MySQL.
The first thing you need is to have a normalized database. This tutorial assumed you do and will not go into how to normalized you database schema.
In my limited experience with MySQL optimization techniques I have found that the single most important thing you can do is to create indices for foreign key columns and other columns used in the where clause. Here is the command to create an index.
[source:sql]
create index <index_name> on <table_name>(<column_name>);
[/source]
You list more than one column names to create an index for more than one column. To list all the indices on a table you can execute the following command.
[source:sql]
show index from <table_name>;
[/source]
And finally, if you would like to delete a index you use the drop command as in the following SQL statement.
[source:sql]
drop index <index_name> on <table_name>;
[/source]
Again, as a general rule of thumb all foreign keys should be indexed. If you feel that the index did not help I recommend you use the explain command to view the execution plan of the database engine. The explain command probably deserves a tutorial of its own. The explain command will return a table detailing the execution plan and the most important column on that table is the type. If you have a value of ALL on that column your SQL statement will do a full table scan, which you should avoid. Here is how to use the explain command.
[source:sql]
explain <sql_statement>;
[/source]
I also have found that after you delete a lot of rows for a particular table you should optimize it.
[source:sql]
optimize table <table_name>;
[/source]
If you have inserted a great number of rows I would analyze the table with SQL similar to the following.
[source:sql]
analyze table <table_name>;
[/source]
If this does not help, you can always throw more memory at the problem. MySQL also has a ton of start up options to control the size of the index cache and other startup parameters with performance issues.
Technorati Tags: database, mysql, optimization, query optimization, sql, explain sql, performance, database normalization, foreign keys