Re: CONCAT and Index in where clause

[Øystein Grøvlen]

Another alternative is to use funcstions on the constant part of the query.
In that case, indexes may be used:

mysql> CREATE TABLE td (year CHAR(4), month CHAR(2), day CHAR(2), INDEX (year), INDEX (month), INDEX(day));
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO td VALUES ('1966','06','05'), ('2012','08','28');
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM td WHERE CONCAT(year, '-', month, '-', day) = '2012-08-28';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | td | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM td WHERE year=SUBSTRING('2012-08-28',1,4) AND month=SUBSTRING('2012-08-28',6,2) AND day=SUBSTRING('2012-08-28',9,2);
+----+-------------+-------+------+----------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | td | ref | year,month,day | year | 5 | const | 1 | Using where |
+----+-------------+-------+------+----------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway