Wednesday, January 14, 2009

How to analyze queries in my-sql and configure an index.

If you have a query in My-Sql and if it is running slow you can always add the explain keyword in front of the SQL to analyze it.

For example executing the following query "explain select * from employee where employee_id='001'; " would give this output.

Note the column where it says "possible_keys" and "keys" in those columns if those columns are showing up as null, then this means that there are no indexes being used.

The next step would be is to create an index and execute the explain query again. If you see the index that you just created then this means the query would use the index and run faster. There are a lot of tradeoffs on when to create indexes so use it with caution... This cool article explains this in depth...

No comments:

Post a Comment