If you need indexes, MySQL will EXPLAIN

by Carey on October 23, 2010

Post image for If you need indexes, MySQL will EXPLAIN

Setting up indexes is essential to keeping your queries nice and quick.

Adding an index is easy – if your using phpMyAdmin its a simple case of finding your table, clicking “Create an Index” and choosing your column or columns.

Adding the right indexes is not quite as easy and can be a bit hit and miss. This is where the handy EXPLAIN function comes in. Find your query that you want to optimise and add EXPLAIN to the beginning, as so:

{code type=php}EXPLAIN SELECT * FROM table;{/code}

This will give you output similar to:

{code type=php} id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table ALL NULL NULL NULL NULL 67 {/code}

If your “key” field is NULL, take a look at the table it is against and make sure any join or order by in your query has an index.

Find more information at the MySQL manual.

Leave a Comment

Previous post:

Next post: