QOT

Working on server-based index analysis I once again recalled a topic I wanted to write about for many times. The topic is redundant “Using where” in query plans. Suppose we have a table like this:

mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) NOT NULL DEFAULT ‘0′,
`b` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`,`b`)
) ENGINE=PBXT AUTO_INCREMENT=25651 DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

now let’s try to analyse 2 simple queries with EXPLAIN SELECT:

1. SELECT a FROM t1 WHERE a>1;
2. SELECT a FROM t1 WHERE a>1 AND b>1;

mysql> explain select * from t1 where a>1 \G
*************************** 1. row ***********
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 200
Extra: Using where; Using index
1 row in set (0.03 sec)

mysql> explain select * from t1 where a>1 and b>1 \G
*************************** 1. row ******************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 200
Extra: Using where; Using index
1 row in set (0.00 sec)

Let’s shortly look at execution paths of the queries. The first one is a typical range scan. MySQL asks storage engine to position the cursor right after the key with value “a=1″, and performs scan in range (1, +inf] returning to client all rows as returned by storage engine. What’s important here is that MySQL is free to send to client all rows as returned by storage engine and no further check is need to ensure that a row matches the WHERE-clause. Now the second query. The major difference from the previous case is that although PRIMARY KEY adjacently includes both fields “a” and “b” the WHERE condition is not anymore a single contiguous open-ended row range. This means that MySQL needs either to scan multiple ranges (for all keys with a>1 fetch subrange with b>1) or fetch the range “a>1″ and do some post-processing then. In any case MySQL needs to check every row returned by storage engine to match the WHERE-condition. This additional filtering is indicated in EXPLAIN with “Using where”. This also implies that for the first query you shouldn’t see “Using where” - it’s just not needed. But as you can see MySQL indicates “Using where” in both cases. I stepped through the query execution code and I can confirm that it is really performed for the first query.

This might be not as big problem as for example bad query plan, but this still hurts performance. For example checking a condition like “a>1″ implies at least 3 virtual function calls for every row…

Using QOT you can easily check whether the WHERE condition can be fully transformed into an index range scan or not:

/*
Query: select a from t1 where a>1
…

related existing indexes:
t1.PRIMARY(a, b): lookup, covering
*/

/*
Query: select a from t1 where a>1 and b>1
…

related existing indexes:
t1.PRIMARY(a, b): lookup (partial), covering
*/

As you can see for the second query QOT indicates that the index can be used to resolve WHERE condition only partially.