CODEWITHSUNDEEP
mysqlperformanceindexingmariadb
tobi.g
tobi.g

Reputation: 946

How to avoid Using temporary; Using filesort on MySql Query

Currently I am facing a rather slow query on a website, which also slows down the server on more traffic. How can I rewrite the query or what index can I write to avoid "Using temporary; Using filesort"? Without "order by" everything works fast, but without the wanted result/order.

SELECT cams.name, models.gender, TIMESTAMPDIFF(YEAR, models.birthdate, CURRENT_DATE) AS age, lcs.viewers
FROM cams
         LEFT JOIN cam_tags ON cams.id = cam_tags.cam_id
         INNER JOIN tags ON cam_tags.tag_id = tags.id
         LEFT JOIN model_cams ON cams.id = model_cams.cam_id
         LEFT JOIN models ON model_cams.model_id = models.id
         LEFT JOIN latest_cam_stats lcs ON cams.id = lcs.cam_id
WHERE tags.name = '?'
ORDER BY lcs.time_stamp_id DESC, lcs.viewers DESC
LIMIT 24 OFFSET 96;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tags NULL const PRIMARY,tags_name_uindex tags_name_uindex 766 const 1 100 Using temporary; Using filesort
1 SIMPLE cam_tags NULL ref PRIMARY,cam_tags_cams_id_fk,cam_tags_tags_id_fk cam_tags_tags_id_fk 4 const 75565047 100 Using where
1 SIMPLE cams NULL eq_ref PRIMARY PRIMARY 4 cam_tags.cam_id 1 100 NULL
1 SIMPLE model_cams NULL eq_ref model_platforms_platforms_id_fk model_platforms_platforms_id_fk 4 cam_tags.cam_id 1 100 NULL
1 SIMPLE models NULL eq_ref PRIMARY PRIMARY 4 model_cams.model_id 1 100 NULL
1 SIMPLE lcs NULL eq_ref PRIMARY,latest_cam_stats_cam_id_time_stamp_id_viewers_index PRIMARY 4 cam_tags.cam_id 1 100 NULL

Upvotes: 0

Views: 1916

Answers (1)

Rick James
Rick James

Reputation: 142198

There are many cases where it is effectively impossible to avoid "using temporary, using filesort".

"Filesort" does not necessarily involve a "file"; it is often done in RAM. Hence performance may not be noticeably hurt.

That said, I will assume your real question is "How can this query be sped up?".

Most of the tables are accessed via PRIMARY or "eq_ref" -- all good. But the second table involves touching an estimated 75M rows! Often that happens as the first table, not second. Hmmmm.

Sounds like cam_tags is a many-to-many mapping table? And it does not have any index starting with name? See this for proper indexes for such a table: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Since the WHERE and ORDER BY reference more than one table, it is essentially impossible to avoid "using temporary, using filesort".

Worse than that, it needs to find all the ones with "name='?'", sort the list, skip 96 rows, and only finally deliver 24.

Upvotes: 0

Related Questions