| Joining multiple tables turns an indexed query into a temp/filesort [message #2173] |
Wed, 07 November 2007 18:07  |
|
-- This first explain without the two feed tables work fine:
EXPLAIN
SELECT raw_score.match_id AS id
, raw_score.score AS raw_score
FROM match_scores AS raw_score
JOIN matches
ON raw_score.match_id = matches.id
/*
LEFT OUTER JOIN feeds_a_source AS source_feed
ON matches.source_item = source_feed.`item_id`
LEFT OUTER JOIN feeds_a_target AS target_feed
ON matches.target_item = target_feed.`item_id`
*/
WHERE raw_score.scoring_section = 'raw'
AND raw_score.run_id = 5
GROUP BY
raw_score.match_id
, raw_score.run_id
, raw_score.score
+----+-------------+-----------+--------+------------------------------------------------------------------------------------------------+--------------------------------------------+---------+---------------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+------------------------------------------------------------------------------------------------+--------------------------------------------+---------+---------------------------------------+------+--------------------------+
| 1 | SIMPLE | raw_score | ref | index_runid_scoring_section_match_id_score,index_match_scores_on_match_id_and_run_id_and_score | index_runid_scoring_section_match_id_score | 56 | const,const | 1776 | Using where; Using index |
| 1 | SIMPLE | matches | eq_ref | PRIMARY | PRIMARY | 4 | text_matching_tool.raw_score.match_id | 1 | Using index |
+----+-------------+-----------+--------+------------------------------------------------------------------------------------------------+--------------------------------------------+---------+---------------------------------------+------+--------------------------+
-- But this query with the feed tables joined in causes it to do a temp/filesort on matches instead. What can I do to avoid this?
EXPLAIN
SELECT raw_score.match_id AS id
, raw_score.score AS raw_score
FROM match_scores AS raw_score
JOIN matches
ON raw_score.match_id = matches.id
LEFT OUTER JOIN feeds_a_source AS source_feed
ON matches.source_item = source_feed.`item_id`
LEFT OUTER JOIN feeds_a_target AS target_feed
ON matches.target_item = target_feed.`item_id`
WHERE raw_score.scoring_section = 'raw'
AND raw_score.run_id = 5
GROUP BY
raw_score.match_id
, raw_score.run_id
, raw_score.score
+----+-------------+-------------+------+------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+----------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+----------------------------------------+------+---------------------------------+
| 1 | SIMPLE | matches | ALL | PRIMARY | NULL | NULL | NULL | 1012 | Using temporary; Using filesort |
| 1 | SIMPLE | raw_score | ref | index_runid_scoring_section_match_id_score,index_match_scores_on_match_id_and_run_id_and_score | index_match_scores_on_match_id_and_run_id_and_score | 8 | text_matching_tool.matches.id,const | 1 | Using where |
| 1 | SIMPLE | source_feed | ref | index_on_id | index_on_id | 13 | text_matching_tool.matches.source_item | 1 | Using index |
| 1 | SIMPLE | target_feed | ref | index_on_id | index_on_id | 13 | text_matching_tool.matches.target_item | 1 | Using index |
+----+-------------+-------------+------+------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+----------------------------------------+------+---------------------------------+
Daniel Einspanjer
|
|
|