MySQL Forums
Forum List  »  Optimizer & Parser

Re: CONCAT and Index in where clause
Posted by: Øystein Grøvlen
Date: August 28, 2012 02:50AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
15168
August 22, 2012 08:45AM
4031
August 23, 2012 09:13PM
Re: CONCAT and Index in where clause
4068
August 28, 2012 02:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.