[RESOLVED] Selecting today's rows from date column with substr() doesn't use index

[2005]

Hello,

For some reason the following query is not using index (birthday column). Is there any way how to fix it or any other method how to select today's rows from date column? Date column caonains dates in this format: 1971-06-24

Thanks.

mysql> explain SELECT id, nickname FROM users_table WHERE substring(birthday,6) = substring(curdate(),6) limit 5;
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | users_table | ALL  | NULL          | NULL | NULL    | NULL | 16522 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+

[CoderDan]

I'd have to check, when I have the time, but I think you may want to ensure the type of field containing teh date is date and not varchar (it may very well be already) and use EXTRACT for the date column and date instead of substr curdate for the php side, and I believe mysql will use teh index then. (But cant promise it without testing)

[2005]

CoderDan,

I checked it - the date field is not varchar. It's date.

[CoderDan]

ok, that's good, in any event, i've had my coffee and checked for you - mysql can't use indexes on any function calls. so extract won't help. if you want it to index you're going to have to add an extra column that contains just the data you want indexed, and add an index to that, and use it in the where

[2005]

Thank you very much CoderDan. I just got another idea for this query:

SELECT nickname from user_table WHERE birthday like '%-07-23';

But it doesn't use index either. Probably because the column is date and not varchar. 🙁

[CoderDan]

yeah, unfortunately you can't have your cake and eat it too 😛 I'd just add the extra column i think, unless this is going to get a lot of activity and you really want to optimize everything all the way, and even then, the extra column might still be the best.

[2005]

ok, I will add an extra column. Thank you.