# Joining multiple tables turns an indexed query into a temp/filesort **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 7, 2007, 5:37pm UTC](https://forums.percona.com/t/joining-multiple-tables-turns-an-indexed-query-into-a-temp-filesort/515 "2007-11-07T17:37:17Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![DEinspanjer](https://avatars.discourse-cdn.com/v4/letter/d/cab0a1/32.png) [@DEinspanjer](https://forums.percona.com/u/DEinspanjer)\ **Post date:** [November 7, 2007, 5:37pm UTC](https://forums.percona.com/t/joining-multiple-tables-turns-an-indexed-query-into-a-temp-filesort/515/1 "2007-11-07T17:37:17Z")
– This first explain without the two feed tables work fine:EXPLAINSELECT 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?EXPLAINSELECT 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 |±—±------------±------------±-----±-----------------------------------------------------------------------------------------------±----------------------------------------------------±--------±---------------------------------------±-----±--------------------------------+ ---
**Author:** ![DEinspanjer](https://avatars.discourse-cdn.com/v4/letter/d/cab0a1/32.png) [@DEinspanjer](https://forums.percona.com/u/DEinspanjer)\ **Post date:** [November 8, 2007, 7:32am UTC](https://forums.percona.com/t/joining-multiple-tables-turns-an-indexed-query-into-a-temp-filesort/515/2 "2007-11-08T07:32:12Z")
So I found this morning that by moving the WHERE clause constraints on raw\_scores into the matches table join, I could eliminate temp/filesort for this set of joins. Unfortunately adding the next join just brings me right back to where I was. Here is the full query with pieces commented out: EXPLAINSELECT raw\_score.match\_id AS id , raw\_score.score AS raw\_score-- , weighted\_score.score AS weighted\_score/\* , title\_score.score AS title\_score , director\_score.score AS director\_score , director\_norm\_score.score AS director\_norm\_score , year\_score.score AS year\_score\*/ FROM match\_scores AS raw\_score JOIN matches ON raw\_score.match\_id = matches.id AND raw\_score.run\_id = 5 AND raw\_score.scoring\_section = ‘raw’ LEFT OUTER JOIN feeds\_disney AS source\_feed ON matches.source\_item = source\_feed.`item_id` LEFT OUTER JOIN feeds\_amg AS target\_feed ON matches.target\_item = target\_feed.`v_id`/\* LEFT OUTER JOIN match\_scores AS weighted\_score ON matches.id = weighted\_score.match\_id AND weighted\_score.scoring\_section = ‘weighted’ AND weighted\_score.run\_id = raw\_score.run\_id LEFT OUTER JOIN match\_scores AS title\_score ON raw\_score.match\_id = title\_score.match\_id AND title\_score.scoring\_section = ‘title’ AND title\_score.run\_id = raw\_score.run\_id LEFT OUTER JOIN match\_scores AS director\_score ON raw\_score.match\_id = director\_score.match\_id AND director\_score.scoring\_section = ‘director’ AND director\_score.run\_id = raw\_score.run\_id LEFT OUTER JOIN match\_scores AS director\_norm\_score ON raw\_score.match\_id = director\_norm\_score.match\_id AND director\_norm\_score.scoring\_section = ‘director\_norm’ AND director\_norm\_score.run\_id = raw\_score.run\_id LEFT OUTER JOIN match\_scores AS year\_score ON raw\_score.match\_id = year\_score.match\_id AND year\_score.scoring\_section = ‘year’ AND year\_score.run\_id = raw\_score.run\_id\*/ GROUP BY raw\_score.match\_id, raw\_score.run\_id, raw\_score.score-- , weighted\_score.score/\* , title\_score.score , director\_score.score , director\_norm\_score.score , year\_score.score\*/ And the explain: ±—±------------±------------±-------±--------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------±--------±---------------------------------------±-----±------------+| 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\_scoring\_section\_and\_run\_id | index\_runid\_scoring\_section\_match\_id\_score | 56 | const,const | 550 | Using where || 1 | SIMPLE | matches | eq\_ref | PRIMARY | PRIMARY | 4 | text\_matching\_tool.raw\_score.match\_id | 1 | || 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 |±—±------------±------------±-------±--------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------±--------±---------------------------------------±-----±------------+ If I uncomment the weighted\_score join: ±—±------------±---------------±-------±--------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------±--------±--------------------------------------------------±-----±---------------------------------------------+| 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\_scoring\_section\_and\_run\_id | index\_scoring\_section\_and\_run\_id | 56 | const,const | 550 | Using where; Using temporary; Using filesort || 1 | SIMPLE | matches | eq\_ref | PRIMARY | PRIMARY | 4 | text\_matching\_tool.raw\_score.match\_id | 1 | || 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 || 1 | SIMPLE | weighted\_score | ref | index\_runid\_scoring\_section\_match\_id\_score,index\_match\_scores\_on\_match\_id\_and\_run\_id\_and\_score,index\_scoring\_section\_and\_run\_id | index\_runid\_scoring\_section\_match\_id\_score | 60 | const,const,text\_matching\_tool.raw\_score.match\_id | 1 | Using index |±—±------------±---------------±-------±--------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------±--------±--------------------------------------------------±-----±---------------------------------------------+ As you can see, I have several more joins after this one. The number of self joins for match\_scores depend on how many scoring\_sections the user has configured. Do you think it is likely I’ll be able to find a combination of query structure and indices that will allow me to avoid temp/filesort? If not, could I have some advice on the best my.cnf settings to make that temp/filesort as painless as possible? As it is, I save the results of this query into a “review” table that is used by the application for dynamic display. This way, resorting, filtering, and pagination don’t impact this big nasty query. If I could get this query down to just a few seconds, I’d be okay with it, but right now, it is taking minutes. ---
**Author:** ![fmpwizard](https://avatars.discourse-cdn.com/v4/letter/f/e47774/32.png) [@fmpwizard](https://forums.percona.com/u/fmpwizard)\ **Post date:** [November 8, 2007, 3:32pm UTC](https://forums.percona.com/t/joining-multiple-tables-turns-an-indexed-query-into-a-temp-filesort/515/3 "2007-11-08T15:32:39Z")
Try adding ORDER BY NULL to that query, as you are not implicitly ordering the results, I think mysql server is sorting based on the group by columns. ---
**Author:** ![DEinspanjer](https://avatars.discourse-cdn.com/v4/letter/d/cab0a1/32.png) [@DEinspanjer](https://forums.percona.com/u/DEinspanjer)\ **Post date:** [November 8, 2007, 6:27pm UTC](https://forums.percona.com/t/joining-multiple-tables-turns-an-indexed-query-into-a-temp-filesort/515/4 "2007-11-08T18:27:57Z")
I didn’t post so, but I actually did try ORDER BY NULL just to make sure. As near as I can tell after reading the MySQL docs on how GROUP BY impacts query optimization, it doesn’t look like it would be possible for my query to avoid at least the filesort and in many cases, the temporary table. This seems to be because I am grouping by columns in multiple tables. The good news is that by adjusting the temp table size variables in my config, I was able to keep these queries solely in memory which provides enough of a performance increase to satisfy my needs. ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [November 8, 2007, 6:46pm UTC](https://forums.percona.com/t/joining-multiple-tables-turns-an-indexed-query-into-a-temp-filesort/515/5 "2007-11-08T18:46:59Z")
No I don’t think that you will be able to get rid of the filesort with a GROUP BY like that. GROUP BY means that MySQL will need that the rows are in order according to the columns involved in the GROUP BY. And since your group by columns come from different tables you can’t have an index that solves this since indexes can’t span over several tables. If the GROUP BY columns are all part of one table and you have an combined index for them then MySQL can choose to use that index as you have seen. Add this to my.cnf to raise the limit for in memory temp tables to 10MB: tmp\_table\_size=10M But does the query actually take minutes? Because it looks like you don’t have that many rows. How much memory are you allowing for index cache? The key\_buffer\_size variable in my.cnf. If the problem is that this cache is to small and mysql has to read the indexes from disk which can slow down things a lot.