Trần Quyết Thắng Trần Quyết Thắng - 1 year ago 61
MySQL Question

Mysql - Query DATETIME field (indexed) performance

In my database, there is table called

which has a
field called

I want to query all the records that has birthday between 05-24 and 06-07 (
) 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
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

Answer Source


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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download