Trần Quyết Thắng Trần Quyết Thắng - 6 months ago 8
MySQL Question

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

Answer

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

Comments