実例 1:レコード数のᮢ値超過 54 過去の SQL 現在の SQL EXPLAIN SELECT id, 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’
実例 1:レコード数のᮢ値超過 55 過去の SQL 現在の SQL EXPLAIN SELECT id, 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 倍 半年前の
実例 1:レコード数のᮢ値超過 56 過去の SQL 現在の SQL EXPLAIN SELECT id, 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 年前と
EXPLAIN 実際に範囲を絞った結果 65 SQL 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' ************ 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ヶ月単位で
実際に範囲を絞った結果 66 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' ************ 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) インデックスが
実際に範囲を絞った結果 67 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' ************ 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) 対象行数は
SELECT user_id, count(user_id) AS counter FROM watchlists WHERE target_table_name = '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 を検索
146 SQL SELECT Feedbacks.user_id, Feedbacks.feedback_user_id, Feedbacks.work_id, Feedbacks.evaluation, Feedbacks.description FROM feedbacks Feedbacks WHERE Feedbacks.evaluation IS NULL AND Feedbacks.created
147 SQL SELECT Feedbacks.user_id, Feedbacks.feedback_user_id, Feedbacks.work_id, Feedbacks.evaluation, Feedbacks.description FROM feedbacks Feedbacks WHERE Feedbacks.evaluation IS NULL AND Feedbacks.created
148 SQL SELECT Feedbacks.user_id, Feedbacks.feedback_user_id, Feedbacks.work_id, Feedbacks.evaluation, Feedbacks.description FROM feedbacks Feedbacks WHERE Feedbacks.evaluation IS NULL AND Feedbacks.created
149 SQL SELECT Feedbacks.user_id, Feedbacks.feedback_user_id, Feedbacks.work_id, Feedbacks.evaluation, Feedbacks.description FROM feedbacks Feedbacks WHERE Feedbacks.evaluation IS NULL AND Feedbacks.created
166 warning 表示 mysql> SHOW WARNINGS\G ************************ 1. row ************************ 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) インデックス改善事例
167 warning 表示 mysql> SHOW WARNINGS\G ************************ 1. row ************************ 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
168 warning 表示 mysql> SHOW WARNINGS\G ************************ 1. row ************************ 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 時に
169 warning 表示 mysql> SHOW WARNINGS\G ************************ 1. row ************************ 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) オプティマイザの
170 warning 表示 mysql> SHOW WARNINGS\G ************************ 1. row ************************ 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 を
171 warning 表示 mysql> SHOW WARNINGS\G ************************ 1. row ************************ 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 から
カバリングインデックスを試す 175 SQL SELECT Feedbacks.evaluation, Feedbacks.created FROM feedbacks Feedbacks WHERE Feedbacks.evaluation IS NULL AND Feedbacks.created