{"id":227,"date":"2007-01-29T02:27:58","date_gmt":"2007-01-29T07:27:58","guid":{"rendered":"http:\/\/www.juixe.com\/techknow\/index.php\/2007\/01\/29\/mysql-optimization-tutorial\/"},"modified":"2007-01-29T02:27:58","modified_gmt":"2007-01-29T07:27:58","slug":"mysql-optimization-tutorial","status":"publish","type":"post","link":"http:\/\/juixe.com\/techknow\/index.php\/2007\/01\/29\/mysql-optimization-tutorial\/","title":{"rendered":"MySQL Optimization Tutorial"},"content":{"rendered":"

A while back I wrote a little introductory MySQL Administration<\/a> 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.<\/p>\n

The first thing you need is to have a normalized database<\/a>. This tutorial assumed you do and will not go into how to normalized you database schema.<\/p>\n

In my limited experience with MySQL optimization techniques<\/b> I have found that the single most important thing you can do is to create indices for foreign key<\/b> columns and other columns used in the where clause. Here is the command to create an index.<\/p>\n

[source:sql]
\ncreate index <index_name> on <table_name>(<column_name>);
\n[\/source]
\n
\nYou 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.<\/p>\n

[source:sql]
\nshow index from <table_name>;
\n[\/source]<\/p>\n

And finally, if you would like to delete a index you use the drop command as in the following SQL statement.<\/p>\n

[source:sql]
\ndrop index <index_name> on <table_name>;
\n[\/source]<\/p>\n

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<\/b> command to view the execution plan of the database engine. The explain<\/a> 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.<\/p>\n

[source:sql]
\nexplain <sql_statement>;
\n[\/source]<\/p>\n

I also have found that after you delete a lot of rows for a particular table you should optimize<\/b> it.<\/p>\n

[source:sql]
\noptimize table <table_name>;
\n[\/source]<\/p>\n

If you have inserted a great number of rows I would analyze<\/b> the table with SQL similar to the following.<\/p>\n

[source:sql]
\nanalyze table <table_name>;
\n[\/source]<\/p>\n

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.<\/p>\n

Technorati Tags: database<\/a>, mysql<\/a>, optimization<\/a>, query optimization<\/a>, sql<\/a>, explain sql<\/a>, performance<\/a>, database normalization<\/a>, foreign keys<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","footnotes":""},"categories":[19,23,3],"tags":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p902K-3F","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/posts\/227"}],"collection":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/comments?post=227"}],"version-history":[{"count":0,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/posts\/227\/revisions"}],"wp:attachment":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/media?parent=227"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/categories?post=227"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/tags?post=227"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}