Mysql - Query DATETIME field (indexed) performance

Question

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

What version of MySQL are you using? 5.7 allows indexing of "virtual columns" — Mark Baker
– Mark Baker, Commented May 24, 2016 at 9:45
its a good idea to use virtual columns. use virtual persitent column, so you can also use a index on it — Bernd Buffen
– Bernd Buffen, Commented May 24, 2016 at 11:57

Bernd Buffen · Accepted Answer · 2016-05-24 09:41:52Z

2

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

answered May 24, 2016 at 9:41

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Trần Quyết Thắng Over a year ago

Hi @Bernd Buffen, i have problem when using between. explain (SELECT * from profiles WHERE birthday2 BETWEEN '0001-01-01' AND '0001-03-06' ) it using index condition. But explain (SELECT * from profiles WHERE birthday2 BETWEEN '0001-01-01' AND '0001-06-06' ) Using where.

Bernd Buffen Over a year ago

whats the problem?

Trần Quyết Thắng Over a year ago

Here is image my screen. link and link. If you require any further information, contact me. Thank you!

Rick James · Accepted Answer · 2016-05-31 19:36:41Z

0

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.

answered May 31, 2016 at 19:36

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Mysql - Query DATETIME field (indexed) performance

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related