If you need indexes, MySQL will EXPLAIN

by Carey on October 23, 2010

Post image for If you need indexes, MySQL will EXPLAIN
  • Share
  • CevherShare
  • Share

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: