# Optimize join query

**URL:** <https://forums.percona.com/t/optimize-join-query/1104>\
**Category:** Other MySQL® Questions\
**Created:** [March 3, 2009, 7:26am UTC](https://forums.percona.com/t/optimize-join-query/1104 "2009-03-03T07:26:16Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![user1](https://avatars.discourse-cdn.com/v4/letter/u/5daacb/32.png) [@user1](https://forums.percona.com/u/user1)\
**Post date:** [March 3, 2009, 7:26am UTC](https://forums.percona.com/t/optimize-join-query/1104/1 "2009-03-03T07:26:16Z")

</div>

Hi,

If any one help to optimize this query. It executes in 0.50 secs.  
But still is there a way to optimize this query?

Explain plan±—±------------±-------------------±-------±---------------------------------------±------------±--------±---------------------------------------------±-----±---------------------------------------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±-------------------±-------±---------------------------------------±------------±--------±---------------------------------------------±-----±---------------------------------------------+| 1 | SIMPLE | ANALYZE\_FILE\_MATCH | ref | ANALYSIS\_ID,MATCHED\_FILE\_ID,PACKAGE\_ID | ANALYSIS\_ID | 9 | const | 1894 | Using where; Using temporary; Using filesort || 1 | SIMPLE | PACKAGE\_NAMES | ALL | PACKAGE\_ID | NULL | NULL | NULL | 1 | Using where || 1 | SIMPLE | DUPLICATE\_FILES | ref | FILE\_ID,FILE\_SET\_ID | FILE\_ID | 9 | tune\_osc2.ANALYZE\_FILE\_MATCH.MATCHED\_FILE\_ID | 1 | Using where || 1 | SIMPLE | DUPLICATE\_FILE\_SET | eq\_ref | PRIMARY | PRIMARY | 8 | tune\_osc2.DUPLICATE\_FILES.FILE\_SET\_ID | 1 | || 1 | SIMPLE | FILE\_INFO | eq\_ref | PRIMARY | PRIMARY | 8 | tune\_osc2.ANALYZE\_FILE\_MATCH.MATCHED\_FILE\_ID | 1 | |±—±------------±-------------------±-------±---------------------------------------±------------±--------±---------------------------------------------±-----±---------------------------------------------+SELECT ANALYZE\_FILE\_MATCH.FILE\_ID, ANALYZE\_FILE\_MATCH.MATCHED\_FILE\_ID, SCORE, ANALYZE\_FILE\_MATCH.MATCHED\_FILE\_NAME, PACKAGE\_NAMES.NAME,FILE\_INFO.FILE\_NAME, FILE\_INFO.UUID\_MSB, FILE\_INFO.UUID\_LSB, DUPLICATE\_FILE\_SET.CHECKSUM,DUPLICATE\_FILE\_SET.FILESIZE FROM ANALYZE\_FILE\_MATCH INNER JOIN PACKAGE\_NAMES ON ANALYZE\_FILE\_MATCH.PACKAGE\_ID=PACKAGE\_NAMES.PACKAGE\_ID INNER JOIN FILE\_INFO ON ANALYZE\_FILE\_MATCH.MATCHED\_FILE\_ID=FILE\_INFO.ID INNER JOIN DUPLICATE\_FILES ON FILE\_INFO.ID=DUPLICATE\_FILES.FILE\_IDINNER JOIN DUPLICATE\_FILE\_SET ON DUPLICATE\_FILES.FILE\_SET\_ID=DUPLICATE\_FILE\_SET.ID WHERE ANALYZE\_FILE\_MATCH.ANALYSIS\_ID=1 ORDER BY ANALYZE\_FILE\_MATCH.FILE\_ID LIMIT 1000,50

Thanks

---

<div class="post-metadata">

**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\
**Post date:** [March 21, 2009, 1:17pm UTC](https://forums.percona.com/t/optimize-join-query/1104/2 "2009-03-21T13:17:04Z")

</div>

Are you referencing any text/blob columns in that join query? If so, MySQL can only sort using a temporary table.

TinyText, for instance, can easily be converted to a varchar(255).