MySQL DBA Help!

Previously I showed you a ‘bad’ query using ‘EXPLAIN’


mysql> EXPLAIN SELECT Population FROM City WHERE Name = 'London' AND CountryCode = 'GBR'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using where
1 row in set (0.00 sec)


So what should you index? We’ll generally you should index frequently used condition columns. Lets say the ‘Name’ column in this example.

So lets create an index on City.Name:

mysql> CREATE INDEX pop_idx ON City(Name);
Query OK, 4079 rows affected (0.08 sec)
Records: 4079 Duplicates: 0 Warnings: 0


Now, lets rerun the EXPLAIN to see what’s changed if anything:


mysql> EXPLAIN SELECT Population FROM City WHERE Name = 'London' AND CountryCode = 'GBR'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ref
possible_keys: pop_idx
key: pop_idx
key_len: 35
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)


Ok, now lets notice the changes. First off when trying to execute the query MySQL, now knows that there is an index it can use (possible_keys) and it decides to use it (from key). From this index it thinks it will only have to scan 1 row (down from 4079 last time). Now there is other information that EXPLAIN is telling us but lets just highlight the key things for novices again.

possible_keys - possible keys MySQL can use
key - index MySQL chose for that part of the query
rows - the number of data rows MySQL thinks it will need to scan

Thus, generally, the lower the ‘rows’ count, the quicker the query!