DATE() MONTH() etc. functions slow down query

Question

I have a table Posts with a lot of records. I want to show user today posts, it is about 3-4 k.

creation_date - DATETIME field. This field has index.

In my query i simple filter records with query:

 SELECT posts.title AS post_title
 WHERE date(posts.creation_date) = DATE('2016-06-11')

This query performing 14 seconds.

Then i changed it to:

SELECT posts.title AS post_title 
WHERE pending_posts.creation_date > CONVERT('2016-06-11', DATETIME) 
AND pending_posts.creation_date < CONVERT('2016-06-11', DATETIME) + INTERVAL 1 DAY

And it takes 0.2 seconds...

Why this happens ? And how do i convert MONTH(posts.creation_date) or YEAR(posts.creation_date) because they slow down queries too, but i need show posts per month and per year.

Answer 1

it is a very bad idea to use functions on a field in the where clause. when you do this MySQL must read ALL rows (FULL TABLE SCAN) to see whats the result anfter the function and the its compare it with the constant.

Its much better to use function on the constant part the its convert only one time and the query can use a index.

for one DAY

SELECT ... WHERE datetimefield between '2016-01-05 00:00:00' AND '2016-01-05 23:59:59';

or

SELECT ... WHERE datetimefield between '2016-01-05 00:00:00' AND '2016-01-05 00:00:00' + INTERVAL 1 DAY

for one month

SELECT ... WHERE datetimefield between '2016-06-01 00:00:00' AND '2016-06-01 00:00:00' + INTERVAL 1 MONTH;

You can see the work of your query in the execution plan:

EXPLAIN SELECT posts.title AS post_title
 WHERE date(posts.creation_date) = DATE('2016-06-11');

and

EXPLAIN SELECT posts.title AS post_title 
WHERE pending_posts.creation_date > CONVERT('2016-06-11', DATETIME) 
AND pending_posts.creation_date < CONVERT('2016-06-11', DATETIME) + INTERVAL 1 DAY