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:

EXPLAIN SELECT * FROM table;

This will give you output similar to:

 id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE table	ALL 	NULL 	NULL 	NULL 	NULL 	67 

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: