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:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM
(
SELECT B.*
FROM
(
SELECT id FROM blogposts
WHERE date_published <= NOW()
AND deleted = 0 AND visible = 1
AND title LIKE '%{de}%'
ORDER BY date_created DESC
LIMIT 0,50
) A
INNER JOIN blogposts B USING (id)
) blogposts
INNER JOIN articles
ON blogposts.id = articles.blogpost_id;
Since you edited the question and stated that you will remove LIKE, now your query should look more like this:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM
(
SELECT B.*
FROM
(
SELECT id FROM blogposts
WHERE date_published <= NOW()
AND deleted = 0 AND visible = 1
ORDER BY date_created DESC
LIMIT 0,50
) A
INNER JOIN blogposts B USING (id)
) blogposts
INNER JOIN articles
ON blogposts.id = articles.blogpost_id;
In the phrase [things omitted], if you do not need anything from blogposts other than the keys, then your query should look like this:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM
(
SELECT id FROM blogposts
WHERE date_published <= NOW()
AND deleted = 0 AND visible = 1
ORDER BY date_created DESC
LIMIT 0,50
) blogposts
INNER JOIN articles
ON blogposts.id = articles.blogpost_id;
CAVEAT
Make sure you build an index that involves the columns deleted, visible, and date_created as follows:
ALTER TABLE blogposts ADD INDEX deleted_visible_date_created (deleted,visible,date_created);
Give it a Try !!!
I have a few suggestions that may help
SUGGESTION #1 : Use index with different column order
ALTER TABLE sales_reps ADD UNIQUE INDEX
(state,account_id,name,platform,client_group,from_date);
Note that state
and account_id
are reversed. That way, there is no need to traverse all rows for a given account_id that would separate enabled from disabled. Only enabled entries for account_id are read.
SUGGESTION #2 : Refactor the Query
Instead of
SELECT *
FROM `sales_reps`
WHERE `sales_reps`.`account_id` = 1
AND state = 'enabled'
AND (clients >= 1 OR revenue >= 100)
ORDER BY name,platform,client_group,from_date
Try this experiment
First run this query
SELECT enabled,accountid,name,platform,client_group,from_date
FROM sales_rep WHERE account_id=1 AND state='enabled'
ORDER BY enabled,accountid,name,platform,client_group,from_date
then run this query
SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
AND clients >= 1 OR revenue >= 100
If the performance of those two queries are fast, then JOIN them as subqueries and see
SELECT B.* FROM
(
SELECT enabled,accountid,name,platform,client_group,from_date
FROM sales_rep WHERE account_id=1 AND state='enabled'
ORDER BY enabled,accountid,name,platform,client_group,from_date
) A LEFT JOIN
(
SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
AND clients >= 1 OR revenue >= 100
) B USING (enabled,accountid,name,platform,client_group,from_date)
WHERE B.enabled IS NOT NULL;
Give it a Try !!!
Best Answer
Using intersect
is almost always an indication that you should have a "composite" index on the columns mentioned.In your case, the index can be either of these:
The order of clauses in the
WHERE
does not matter -- the Optimizer is free to reorder things. It does not matter to the optimization.On the other hand, the order of columns in a composite index does matter. (But it does not happen to matter in this case.)
Further explanation: For the "intersect" technique, this happens:
INDEX(user_id)
for 32366; collect thePRIMARY KEYs
for those rows.INDEX(ref_urs)
for 'http...'; collect thePRIMARY KEYs
for those rows.PRIMARY KEYs
.With a composite index: