SQL: poor performance despite index

Just now I was confronted with the following performance problem of a SQL database: In a SQL table called "users" with ~1,000,000 entries, it took an unbearable 28 seconds to determine the number of registrations on today's day. In the "created" column, the times of the registrations were stored in the format "Y-m-d H:i:s".


The query used

SELECT COUNT(ID) FROM users WHERE DATE(created) = '2014-12-21';

led to this loading time, although there was already an index in the "created" column of the DATETIME format

ALTER TABLE `users` ADD INDEX `created` (`created`);

had been set. The reason for the poor performance is the function call of DATE: The indexed column “created” is sent by the function DATE and makes the index ineffective. The Query Optimizer only knows "created", but not the result of DATE (created). The optimized query

SELECT COUNT(ID) FROM users WHERE created BETWEEN '2014-12-21' AND '2014-12-21 23:59:59';

leads to the desired result in a time of 0.4 seconds.

Back