MySQL Forums
Forum List  »  Newbie

Re: Any way to optimize join to find rows without conditional foreign rows?
Posted by: Rick James
Date: February 14, 2009 02:40AM

Oh, the classic problem.

Can you get rid of
AND (KeywordsRequest.status = 'success' OR KeywordsRequest.status = 'active')
? It has two problems:
* OR -- this usually eliminates use of indexes
* Those values are probably rarely 'fail' or 'inactive', yet the engine has to do a lot of work to get around them.

"KeywordsRequest.id IS NULL" may fail into the above issue.

Rows_sent: 568 Rows_examined: 1826718 --
This is a big clue. It says it can't really use the index, but has to gather 1.8M rows before filtering things down to 568.

"I've got indexes on almost every column" -- not good:
* More indexes -> more time on INSERTing
* Sounds like single-column indexes. You possibly need a "compound" index.

Have you tried INDEX (source_id, created) ? (I can't see enough to know if that is optimal.)

Why do you have
GROUP BY Keyword.id
? Perhaps that can be eliminated? Or replaced by SELECT DISTINCT?

See if this gives the same result and is faster:
SELECT DISTINCT k.id, k.keyword
    FROM `keywords` as k
    JOIN
    ( SELECT keyword_id, created
      FROM `keywords_requests`
      WHERE (status = 'success' OR status = 'active')
        AND source_id = '29'
        AND created > FROM_UNIXTIME(1234551323)
        AND id IS NULL
    ) AS kr
    WHERE kr.keyword_id = k.id
    ORDER BY kr.created ASC;

For further analysis of this slow SELECT, please provide
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]

Options: ReplyQuote


Subject
Written By
Posted
Re: Any way to optimize join to find rows without conditional foreign rows?
February 14, 2009 02:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.