Home » Performance » MySQL » Joining multiple tables turns an indexed query into a temp/filesort
Joining multiple tables turns an indexed query into a temp/filesort [message #2173] Wed, 07 November 2007 18:07 Go to previous message
DEinspanjer  is currently offline DEinspanjer
Messages: 3
Registered: November 2007
Location: Cambridge MA
Junior Member

-- 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

Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic:MySQL slow with innodb table of +-55MB
Next Topic:Read/Write Ratio For A Table
Goto Forum:

  


Current Time: Fri Jul 10 04:51:37 EDT 2009

Total time taken to generate the page: 0.02163 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.