Jak wiadomo, podstawą wydajnej pracy bazy danych są odpowiednio dobrane indeksy. Niestety, to nie wystarczy. Bardzo częstym błędem, popełnianym nawet przez doświadczonych programistów, jest stosowanie w warunku WHERE funkcji działającej na wartości kolumny. O co dokładnie chodzi ilustruje podany niżej przykład. Zakładamy tabelę o następującej strukturze:

mysql> SHOW CREATE TABLE tab1\G
*************************** 1. row ***************************
Table: tab1
Create Table: CREATE TABLE `tab1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` int(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3102 DEFAULT CHARSET=latin2
1 row in set (0,00 sec)

Kolumna `timestamp`, jak sama nazwa wskazuje, przechowuje datę w postaci uniksowego timestampa. Załóżmy, że w wyniku zapytania chcemy dostać tylko te rekordy, których timestamp jest późniejszy niż 2 stycznia 2004 roku. Sprawdzamy jak wygląda plan takiego zapytania:

mysql> EXPLAIN SELECT * FROM tab1 WHERE FROM_UNIXTIME(timestamp) >= '2004-01-02 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL | 3101 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0,00 sec)

Jako że w warunku WHERE mamy pole timestamp, nakładamy na niego indeks i sprawdzamy, czy to coś zmieniło:

mysql> ALTER TABLE tab1 ADD INDEX idx_timestamp (timestamp);
Query OK, 3101 rows affected (0,02 sec)
Rekordów: 3101  Duplikatów: 0  Ostrzeżeń: 0

mysql> EXPLAIN SELECT * FROM tab1 WHERE FROM_UNIXTIME(timestamp) >= '2004-01-02 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL | 3101 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0,00 sec)

Niestety, oczekiwanej zmiany nie ma. Dodany indeks nie pojawił się nawet na liście możliwych do wykorzystania. Dlaczego tak się stało?

Wyjaśnienie jest proste. Optimizer MySQL może wykorzystać indeks na danej kolumnie jedynie wtedy, jeśli jest ona w postaci „wolnej”. Pod postać „przetworzoną” łapią się nie tylko funkcje, co i wyrażenia arytmetyczne. W naszym przykładzie na kolumnę `id` nałożony jest klucz główny. Jeśli napiszemy zapytanie tak, że kolumna id będzie w postaci „przetworzonej” – jako część wyrażenia arytmetycznego, nie będzie dało się z niego skorzystać. Jeśli jednak sami rozwiążemy to proste równanie, klucz główny jest wykorzystywany.

mysql> EXPLAIN SELECT * FROM tab1 WHERE id+10=20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL | 3101 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0,00 sec)

mysql> EXPLAIN SELECT * FROM tab1 WHERE id=10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tab1  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0,00 sec)

Co ciekawe, jeśli wyrażenie algebraiczne będzie po prawej stronie znaku równości, to optimizer będzie w stanie sobie je uprościć do postaci ostatecznego wyniku:

mysql> EXPLAIN SELECT * FROM tab1 WHERE id=5+5;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tab1  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0,00 sec)

Ważne jest to, aby wartość z indeksowanej kolumny była jedynym elementem po swojej stronie równania. To samo jest z funkcjami. Jeśli uwolnimy wartość kolumny timestamp i zamiast konwertować ją do formatu daty, przekonwertujemy datę do formatu timestampu, indeks będzie wykorzystywany:

mysql> EXPLAIN SELECT * FROM tab1 WHERE timestamp >= UNIX_TIMESTAMP('2004-01-02 00:00:00');
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | tab1  | range | idx_timestamp | idx_timestamp | 5       | NULL |   30 | Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0,00 sec)

Niestety, optimizer MySQL nie jest na tyle inteligentny, żeby samodzielnie poradzić sobie z takimi przekształceniami i to na administratora lub programistę spada obowiązek pamiętania o tego typu drobnych niuansach. Brak wykorzystania indeksu w przypadku większych tabel będzie skutkował znacznym spowolnieniem działania zapytań, a rozwiązanie jest przecież banalne.