CODEWITHSUNDEEP
phpmysqldatetimeindexing
Trần Quyết Thắng
Trần Quyết Thắng

Reputation: 23

Mysql - Query DATETIME field (indexed) performance

In my database, there is table called Profile which has a DATETIME field called birthday.

I want to query all the records that has birthday between 05-24 and 06-07 (mm-dd) regardless of what year it is and I used the following query:

DATE_FORMAT(`Profile`.`birthday`, "%m-%d") >= '05-24' 
AND DATE_FORMAT(`Profile`.`birthday`, "%m-%d") <  '06-07'

The problem is by doing this way, the index on birthday column has no use, the query is too slow and I want to improve the speed of my query

Please show me if there is any workaround that could still do the job and improve the query performance at the same time

Upvotes: 0

Views: 1210

Answers (2)

Rick James
Rick James

Reputation: 142540

Add another column

mmdd CHAR(5) CHARACTER SET ascii
and
INDEX(mmdd)

Set it using an UPDATE with SET mmdd = DATE_FORMAT(..., "%m-%d")

As a string (CHAR), it would compare 'correctly' unless you wrap around a year boundary (eg, November to February).

For 5.6 -- do it explicity.
For 5.7 or MariaDB -- use virtual column.

Keep in mind that if your date range is "too big" (somewhere around 2 months), the optimizer will ignore the index and do a full table scan. This is deliberate, and actually optimal.

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

Yes,

the reason is that you use a FUNCTION on a field in WHERE. This means that MySQL must read all Records of the Table (FULL TABLE SCAN).

You can add a new field birthday2 where you store only day and month with a normalized year like

UPDATE yourTable set birthday2 = DATE_FORMAT(Profile.birthday, "%m-%d-0001");

Ten you can select like

SELECT * from yourTable
WHERE birthday2 BETWEEN ''05-24-0001' AND '06-07-001';

And if you set a index on this field it will be used

Upvotes: 2

Related Questions