MySQL DBA Help!

Sometimes when you run a query it runs really quickly and sometimes it runs really slowly. Why does this occur? Well MySQL has the ‘EXPLAIN’ syntax to see how MySQL will execute a query.

Loosely, a query is bad if the conditions (WHERE a=b) are against unindexed rows.

Let me give you an example. Say I have a table City with some columns and I want to find the Population of the City ‘London’ in the UK. So I’d run a query like:


mysql> SELECT Population FROM City WHERE Name = 'London' AND CountryCode = 'GBR';
+------------+
| Population |
+------------+
| 7285000 |
+------------+
1 row in set (0.00 sec)

Now you can see how MySQL will execute the query by prefixing it with ‘EXPLAIN’ like:

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)

Now the key thing you can deduce from the above for novices is that MySQL was not able to use any indexes. You can see that as ‘possible_keys’ (indexes) is NULL and ‘key’ (the index mysql chose) is also ‘NULL’. Also, MySQL thinks it will need to scan though around 4079 rows to find the answer.

So from that you can tell that this query is ‘bad’ in that MySQL will need to check 4079 columns before it can find the answer. So if you run this query over and over again, it may be wise to index one of both of the condition columns like ‘Name’ and/or ‘CountryCode’ so MySQL doesnt have to check 4079 rows every time.