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.
