How can MySQL use index for function in where?

Question

How can mysql use ts index in this query? (EXTRA: Using where; Using index)

EXPLAIN SELECT * FROM times
WHERE UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME(ts), 'GMT', 'EET')) > 10000;


| ID | SELECT_TYPE | TABLE |  TYPE | POSSIBLE_KEYS |     KEY | KEY_LEN |    REF | ROWS |                    EXTRA |
|----|-------------|-------|-------|---------------|---------|---------|--------|------|--------------------------|
|  1 |      SIMPLE | times | index |        (null) | PRIMARY |       4 | (null) |   10 | Using where; Using index |

The schema:

CREATE TABLE times(
    ts int(11) NOT NULL COMMENT 'timestamp',
    PRIMARY KEY (ts)
);

INSERT INTO times VALUES (0), (1000), (5000), (10000), (15000),
(20000), (600000), (7000000), (80000000), (900000000);

SQL fiddle link: http://sqlfiddle.com/#!9/6aa3d/8

MySQL uses index. Why and how?

Could you provide me with MySQL documentation page describing this feature?

Answer 1

A BTREE index in MySQL (which most indexes are) serves two purposes: it can be used to randomly and/or sequentially access the data of a table based on a particular key. The index can also be used to satisfy a query if it happens to contain all the columns the query requires. In your case, MySQL uses your index for the latter purpose but not the former.

The query you have shown can be entirely satisfied from the index you have defined. That's called a covering index. This happens to be be a trivial case, because there's only one column in the table and it is indexed. Take a look at POSSIBLE_KEYS in your explain resultset, and notice that there are none.

This is a little confusing. MySQL is using the index to satisfy the query because all the columns it requires are in the index. However, it is not accessing the index using a key value. Instead, it has to scan the whole thing.

No query which applies a function to a column name can be satisfied by random-accessing an index using a key. In your sample case your WHERE clause has the form

WHERE f(g(h(column))) > value

If you recast that inequality as

WHERE column > H(G(F(value)))

MySQL will employ the key to do its search. It can do this because it converts the right-hand-side of that expression to a constant, then uses the constant to random-access the index at the first eligible value. That's called an index range scan.

Some references: http://planet.mysql.com/entry/?id=661727

http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/

Answer 2

This is the same as:

SELECT * 
  FROM times
 WHERE ts > UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME(10000), 'EET', 'GMT'));

Which can use the index happily.