Tags: explain

Making queries more efficient 2) Optimisation of sql.

By admin on Mar 19, 2009 | In Formula 1 - More advanced stuff, Highway Maintenance - DBA stuff | Send feedback »

One major aid in examining code that you think is operating inefficiently, is to use the EXPLAIN command.
mysql manual

The EXPLAIN command tells you how Mysql intends to execute a particular statement. We can determine from the results whether Mysql has chosen an optimal way of getting the data. Two of the columns are particularly worth looking at. The first is the Type column (that shows the access type Mysql is using for the query)
In order of least efficient access to most efficient, these are the values that should appear in the Type column:
All - index - range - index_subquery - unique_subquery - index_merge - ref_or_null - ref - eq_ref - const - system

The next is the Extra column (this shows extra information concerning this particular access strategy).

Now if we use the Explain command on a query (looking at a data set of 2921 records, recorded against 50 cities) we get:

Example script

Code:

explain
 select a.* from city a
 where a.city_id in
 (select b.city_id
from people b
 group by b.city_id
 having max(b.income) > 68500) \G

*************** 1. row ****
id: 1
select_type: PRIMARY
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50
Extra: Using where
*************** 2. row ****
id: 2
select_type: DEPENDENT SUBQUERY
table: b
type: index
possible_keys: NULL
key: city_id
key_len: 3
ref: NULL
rows: 2921
Extra: Using filesort
2 rows in set (0.06 sec)

Note that the subquery row in the Extra column states that "using filesort" is needed. If Filesort is shown it means that the sort can't be used from an index ie the sort is carried out in memory (and sometimes continued on disk if there are memory issues). This is inefficient, and will increasingly take up more resources as the table increases. Even with a small data set, having to sort 2921 rows may have an adverse impact.
In this case, there already is an index on the field city_id in the people table. In fact it's not the index issue that is making this query slow - it's the fact that Mysql processes subqueries very inefficiently.Now, according to the following link, it looks like Mysql version 6.0.x is much more efficient at processing subqueries using the 'IN' clause

big dba head

In the meantime, the best way to optimise this type of query is by writing it in a Join format.

Code:

explain      
select a.state, a.capital, a.largest, a.city_id        
from city a      
inner join people p on a.city_id = p.city_id      
group by a.state, a.capital, a.largest, a.city_id      
having max(p.income) > 68500 \G

Now if we run the Explain command again we get:

**************** 1. row ***
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50
Extra: Using temporary; Using filesort
**************** 2. row ***
id: 1
select_type: SIMPLE
table: p
type: ref
possible_keys: city_id
key: city_id
key_len: 3
ref: cookbook.a.city_id
rows: 40
Extra: Using where

Yikes - the first row still uses filesort! However this is no longer performed on the inefficient subquery; moreover it only applies to the 50 rows brought back in the city grouping (in the initial statement the filesort was performed on 2921 rows in the subquery). Incidentally, this query ran on average by just .2 of a second faster; however in database terms 2921 records is relatively small, and the larger the data-set, the greater the gain in speed will be by using table joins.

Of course, although re-writing your queries to use joins will help immensely, it doesn't mean that the role of indexes has to be forgotten. On the contrary, the importance of using indexes correctly is crucial. I hope to look in more detail at this in my next article. :

Tags: explain, joins, optimisation