SQL: poor performance despite index

I was just confronted with the following performance problem of an SQL database: In an SQL table called "users" with ~ 1,000,000 entries, it took an unacceptable 28 seconds to determine the number of registrations today. The times of the registrations were saved in the “Ymd H: i: s” format in the “created” column.


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