How expensive is a WHERE clause in MySQL?

October 31, 2008

Author

Baron Schwartz

Benchmarks

Insight for DBAs

Share this Post:

This is a fun question I’ve been wanting to test for some time.Â How much overhead does a trivial WHERE clause add to a MySQL query?Â To find out, I set my InnoDB buffer pool to 256MB and created a table that’s large enough to test, but small enough to fit wholly in memory:

<br>CREATE TABLE `t` (<br>`a` date NOT NULL<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1;<br><br>insert into t(a) values(current_date);<br>insert into t select * from t;<br>

1	<br>CREATE TABLE `t` (<br>`a` date NOT NULL<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1;<br><br>insert into t(a) values(current_date);<br>insert into t select * from t;<br>

I repeated the last statement until it got slow enough that I thought I could do a reasonable test; at this point there were 8388608 rows. I then optimized the table and restarted MySQL. The table ended up at 237MB.

Now let’s see how long a table scan with no WHERE clause takes:

<br>mysql> select sql_no_cache count(*) from t;<br>+----------+<br>| count(*) |<br>+----------+<br>|  8388608 |<br>+----------+<br>1 row in set (5.23 sec)<br>

1	<br>mysql> select sql_no_cache count() from t;<br>+----------+<br>\| count() \|<br>+----------+<br>\| 8388608 \|<br>+----------+<br>1 row in set (5.23 sec)<br>

I repeated this ten times and averaged the time: 5.01 seconds, ranging from 4.83 to 5.17 seconds. Next I ran it with a trivial WHERE clause:

<br>mysql> select count(*) from t where a = current_date;<br>

1	<br>mysql> select count(*) from t where a = current_date;<br>

(Pop quiz: do I need SQL_NO_CACHE on this query?) The average execution time for this query is 7.96, ranging from 7.65 to 8.15 seconds. So apparently the overhead of the WHERE clause is about 2.95 seconds, or 351 nanoseconds per row. It doesn’t seem like much per row, but it adds up to about an extra 60% cost for the query. If I add another WHERE clause,

<br>mysql> select count(*) from t where a = current_date and left(a, 10) = '2008-10-29';<br>

1	<br>mysql> select count(*) from t where a = current_date and left(a, 10) = '2008-10-29';<br>

The average time is 9.39 seconds, or an added 87% overhead.

It would be interesting, in an academic kind of way, to test different data types and various complexities of WHERE clauses, but I’m not really interested enough to spend much time on it. I just wondered whether the WHERE clause would even be noticeable. This test doesn’t really reveal anything you can do to make your queries faster — you usually can’t optimize the WHERE clause itself, only the way that the MySQL optimizer chooses to apply the clause to indexes. (However, as I found out some time ago, some date functions are faster than others. That would be interesting to look into more.)