Optimize join query

MySQL & MariaDB Other MySQL® Questions
1

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

2

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