I have a table Posts with a lot of records. I want to show user today posts, it is about 3-4 k.
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
YEAR(posts.creation_date) because they slow down queries too, but i need show posts per month and per year.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT posts.title AS post_title WHERE posts.date_creation_date = DATE('2016-06-11')