joinしているテーブルでUsing temporary; Using filesort が出て遅い時
Railsを触るヽ('ω')ノ三ヽ('ω')ノ
とある日MySQLのチューニングをしていると Using temporary; Using filesort に出会う。
記事テーブルとコメントテーブルをjoinしているようなクエリ。とあるカテゴリの最新のコメント3件がほしい、またコメントしている記事の情報もほしい。
WHERE句で使っている検索条件のテーブルが、2つのテーブルでまたがってしまっている場合、一度JOINした後にSORTなどいろいろやらねばならず無駄である。先にレコードを絞ってからjoinしたほうが効率的。*1
そのため、1つのテーブルに検索条件を寄せて、Using filesortが出ないようにした。
NG case
SELECT `comments`.*, `post`.* FROM `comments` INNER JOIN `posts` ON `posts`.`id` = `comments`.`post_id` WHERE `posts`.`category_id` = 161 -- ☆ここがまずい AND ( comments.status = 1 AND comments.created_at <= '2016-03-27 18:11:47' ) ORDER BY comments.created_at DESC LIMIT 3; +----+-------------+----------+------+-----------------------------+-----------------+---------+--------------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+-----------------------------+-----------------+---------+--------------------------------+------+---------------------------------+ | 1 | SIMPLE | posts | ref | PRIMARY,category_id_idx | category_id_idx | 5 | const | 694 | Using temporary; Using filesort | | 1 | SIMPLE | comments | ref | post_id_idx,created_at_idx | post_id_idx | 5 | development.posts.id | 21 | Using where | +----+-------------+----------+------+-----------------------------+-----------------+---------+--------------------------------+------+---------------------------------+ 2 rows in set (0.00 sec)
OK case
SELECT `comments`.*, `post`.* FROM `comments` INNER JOIN `posts` ON `posts`.`id` = `comments`.`post_id` WHERE `comments`.`category_id` = 161 -- ☆ 一度commentsテーブルを絞ってからjoinする AND ( comments.status = 1 AND comments.created_at <= '2016-03-27 18:11:47' ) ORDER BY comments.created_at DESC LIMIT 3; +----+-------------+----------+--------+---------------------------------------------+----------------+---------+----------------------------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+--------+---------------------------------------------+----------------+---------+----------------------------------------+-------+-------------+ | 1 | SIMPLE | comments | range | post_id_idx,category_id_idx,created_at_idx | created_at_idx | 6 | NULL | 89540 | Using where | | 1 | SIMPLE | posts | eq_ref | PRIMARY | PRIMARY | 4 | development.comments.post_id | 1 | NULL | +----+-------------+----------+--------+---------------------------------------------+----------------+---------+----------------------------------------+-------+-------------+ 2 rows in set (0.00 sec)
いいね!
*1:JOINせずに、先にcommentsテーブルを取得して、それからpostsテーブルを引く二回に分ける方法もありだと思います。