Announcement

Announcement Module
Collapse
No announcement yet.

Optimize join query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimize join query

    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.FIL ESIZE FROM ANALYZE_FILE_MATCH INNER JOIN PACKAGE_NAMES ON ANALYZE_FILE_MATCH.PACKAGE_ID=PACKAGE_NAMES.PACKAG E_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).

    Comment

    Working...
    X