I need to optimize the following query:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE blogposts.deleted = 0
AND blogposts.title LIKE '%{de}%'
AND blogposts.visible = 1
AND blogposts.date_published <= NOW()
ORDER BY blogposts.date_created DESC
LIMIT 0 , 50
EXPLAIN SELECT gives me the following result:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE articles ALL blogpost_id NULL NULL NULL 6915 Using temporary; Using filesort
1 SIMPLE blogposts eq_ref PRIMARY PRIMARY 4 articles.blogpost_id 1 Using where
Why does it first take the articles and then the blogposts? Is it because blogposts have more entries?
And how can I improve the query so that the articlepost can use an index?
Update:
An index is set on blogposts.date_created.
Removing the blogposts.title LIKE condition and the date_published <= NOW() doesn't do anything.
When I remove the "articles.id AS articleid" it can use the blogpost_id Index on articles…
Sounds strange to me, someone knows why? (because I actually need it..)
The new explain looks like this:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE articles index blogpost_id blogpost_id 4 NULL 6915 Using index; Using temporary; Using filesort
1 SIMPLE blogposts eq_ref PRIMARY PRIMARY 4 articles.blogpost_id 1 Using where
Best Answer
I took a closer look at the query and you might be able to redesign it. Here is what I mean:
The LIMIT 0,50 portion of the query seems to be made busy in the query last.
You can improve the layout of the query by doing the following:
Step 1) Create an inline query to gather only keys. In this case, the id for blogposts.
Step 2) Impose any WHERE, ORDER BY and GROUP BY clauses on the inline query bringing keys.
Step 3) Impost the LIMIT clause as the last step of making the inline query.
Step 4) Join the inline query with the blogpost table in the event you need additional columns from blogpost as a blostpost temptable
Step 5) Join this new blogpost temptable with the articles table.
Steps 1-3 is meant to create a temptable with exactly 50 rows and include the blogpost id. Then, perform all JOINs dead last.
With these steps applied to your original query, you should have this:
Since you edited the question and stated that you will remove LIKE, now your query should look more like this:
In the phrase [things omitted], if you do not need anything from blogposts other than the keys, then your query should look like this:
CAVEAT
Make sure you build an index that involves the columns deleted, visible, and date_created as follows:
Give it a Try !!!