work_id, user_id, status FROM results WHERE created < ‘2011-03-26 10:25:36’ EXPLAIN SELECT id, work_id, user_id, status FROM results WHERE created < ‘2021-03-26 10:25:36’
work_id, user_id, status FROM results WHERE created < ‘2011-03-26 10:25:36’ EXPLAIN SELECT id, work_id, user_id, status FROM results WHERE created < ‘2021-03-26 10:25:36’ 833 倍 半年前の 一覧を検索
work_id, user_id, status FROM results WHERE created < ‘2011-03-26 10:25:36’ EXPLAIN SELECT id, work_id, user_id, status FROM results WHERE created < ‘2021-03-26 10:25:36’ 833 倍 10 年前と 現在を比較
FROM results WHERE created BETWEEN '2021-02-26 10:25:36' AND '2021-03-26 10:25:36' ************ 1. row *********** * id: 1 select_type: SIMPLE table: results partitions: NULL type: range possible_keys: created key: created key_len: 6 ref: NULL rows: 433396 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec) 1ヶ月単位で 絞り込む
'Works' GROUP BY user_id ORDER BY counter DESC LIMIT 10 SELECT user_id, count(user_id) AS counter FROM watchlists WHERE target_table_name = 'Packages' GROUP BY user_id ORDER BY counter DESC LIMIT 10 計測:カーディナリティの偏り 81 Pakages の SQL Works の SQL ユーザー別 お気に入り TOP 10 を検索
1 select_type: SIMPLE table: results partitions: NULL type: range possible_keys: created key: created key_len: 6 ref: NULL rows: 433396 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.03 sec) mysql> EXPLAIN -> SELECT -> id, -> work_id, -> user_id, -> status -> FROM -> results -> WHERE -> created BETWEEN '2021-02-26 10:25:36' AND '2021-03-26 10:25:36'\G
メモリとファイルでクイックソート → 駆動表と ORDER BY が別テーブルの場合 → GROUP BY による暗黙の ORDER BY でも出る → LIMIT 前のレコード数が多ければ遅い → Using temporary が一緒に出ることが多い EXPLAIN の主な見方 142 Extra
Level: Note Code: 1003 Message: /* select#1 */ select `db_name`.`Feedbacks`.`evaluation` AS `evaluation`,`db_name`.`Feedbacks`.`created` AS `created` from `db_name`.`feedbacks` `Feedbacks` where (isnull(`db_name`.`Feedbacks`.`evaluation`) and (`db_name`.`Feedbacks`.`created` between '2021-09-16 15:19:21' and '2021-09-18 15:19:21')) 1 row in set (0.00 sec) インデックス改善事例
Level: Note Code: 1003 Message: /* select#1 */ select `db_name`.`Feedbacks`.`evaluation` AS `evaluation`,`db_name`.`Feedbacks`.`created` AS `created` from `db_name`.`feedbacks` `Feedbacks` where (isnull(`db_name`.`Feedbacks`.`evaluation`) and (`db_name`.`Feedbacks`.`created` between '2021-09-16 15:19:21' and '2021-09-18 15:19:21')) 1 row in set (0.00 sec) EXPLAIN EXTENDED (追加情報) インデックス改善事例
Level: Note Code: 1003 Message: /* select#1 */ select `db_name`.`Feedbacks`.`evaluation` AS `evaluation`,`db_name`.`Feedbacks`.`created` AS `created` from `db_name`.`feedbacks` `Feedbacks` where (isnull(`db_name`.`Feedbacks`.`evaluation`) and (`db_name`.`Feedbacks`.`created` between '2021-09-16 15:19:21' and '2021-09-18 15:19:21')) 1 row in set (0.00 sec) EXPLAIN 時に 常に表示される インデックス改善事例
Level: Note Code: 1003 Message: /* select#1 */ select `db_name`.`Feedbacks`.`evaluation` AS `evaluation`,`db_name`.`Feedbacks`.`created` AS `created` from `db_name`.`feedbacks` `Feedbacks` where (isnull(`db_name`.`Feedbacks`.`evaluation`) and (`db_name`.`Feedbacks`.`created` between '2021-09-16 15:19:21' and '2021-09-18 15:19:21')) 1 row in set (0.00 sec) オプティマイザの 最適化の際のメモ インデックス改善事例
Level: Note Code: 1003 Message: /* select#1 */ select `db_name`.`Feedbacks`.`evaluation` AS `evaluation`,`db_name`.`Feedbacks`.`created` AS `created` from `db_name`.`feedbacks` `Feedbacks` where (isnull(`db_name`.`Feedbacks`.`evaluation`) and (`db_name`.`Feedbacks`.`created` between '2021-09-16 15:19:21' and '2021-09-18 15:19:21')) 1 row in set (0.00 sec) SQL を 書き換えたよ インデックス改善事例
Level: Note Code: 1003 Message: /* select#1 */ select `db_name`.`Feedbacks`.`evaluation` AS `evaluation`,`db_name`.`Feedbacks`.`created` AS `created` from `db_name`.`feedbacks` `Feedbacks` where (isnull(`db_name`.`Feedbacks`.`evaluation`) and (`db_name`.`Feedbacks`.`created` between '2021-09-16 15:19:21' and '2021-09-18 15:19:21')) 1 row in set (0.00 sec) IS NULL から isnull 関数に 書き換え インデックス改善事例
partitions: NULL type: range possible_keys: work_id,target_table_name_created_target_id key: target_table_name_created_target_id key_len: 774 ref: NULL rows: 635 filtered: 100.00 Extra: Using where; Using index; Using temporary; Using filesort 1 row in set, 1 warning (0.00 sec) 205 EXPLAIN Using temporaty, Using fi lesort インデックス改善事例
partitions: NULL type: range possible_keys: work_id,target_table_name_created_target_id key: target_table_name_created_target_id key_len: 774 ref: NULL rows: 635 filtered: 100.00 Extra: Using where; Using index; Using temporary; Using filesort 1 row in set, 1 warning (0.00 sec) 206 EXPLAIN GROUP BY による 暗黙の ORDER BY によるもの インデックス改善事例
partitions: NULL type: range possible_keys: target_table_name_created_target_id key: target_table_name_created_target_id key_len: 774 ref: NULL rows: 635 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) 210 GROUP BY を外した EXPLAIN インデックス改善事例
partitions: NULL type: range possible_keys: target_table_name_created_target_id key: target_table_name_created_target_id key_len: 774 ref: NULL rows: 635 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) 211 GROUP BY を外した EXPLAIN Using temporaty, Using fi lesort が 消える インデックス改善事例