# Features of the test table: # * Synthetic primary key (`id`). # * Secondary key. # * Index with a covered and non-covered column. --echo # --echo # Preparing tables --echo # CREATE TABLE `t` ( `id` BIGINT NOT NULL, `other_id` BIGINT NOT NULL, `covered_column` VARCHAR(50) NOT NULL, `non_covered_column` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), INDEX `index_other_id_covered_column` (`other_id`, `covered_column`) ) ENGINE=InnoDB; --echo # Inserting a bunch of data into table t. START TRANSACTION; --disable_query_log let $n = 1000000; while ($n) { eval INSERT INTO `t` (`id`, `other_id`, `covered_column`, `non_covered_column`) VALUES ($n, $n % 1000, '$n', '$n'); dec $n; } --enable_query_log COMMIT; --echo # Shifting some of the data to the "end" of the table. UPDATE `t` SET `id`=`id`+1000000 WHERE `other_id` = 555; --echo --echo --echo --echo # --echo # Warming the cache so that the timed queries are from cache. --echo # SELECT SUM(`non_covered_column`) FROM `t` WHERE `other_id` = 555; SET optimizer_trace = "enabled=on", optimizer_trace_max_mem_size = 1000000, end_markers_in_json = ON; --echo --echo --echo --echo ### --echo ### --echo ### Testing SELECT ... WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT ... --echo ### --echo ### --echo --echo --echo --echo # --echo # Running with a small LIMIT which causes reconsideration. --echo # EXPLAIN SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1; let $start_time = `SELECT UNIX_TIMESTAMP(NOW(6))`; SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1; SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%index_order_summary%plan_changed%false%') AS should_be_1 FROM information_schema.optimizer_trace; let $run_time = `SELECT UNIX_TIMESTAMP(NOW(6)) - $start_time`; let $query_was_slow = `SELECT $run_time > 0.1 AS query_was_slow`; if ($query_was_slow) { --echo # Run time exceeded limit! Time was: $run_time } if (!$query_was_slow) { --echo # Run time was as expected (fast, < 0.1s). } --echo --echo --echo --echo # --echo # Running with a larger LIMIT which doesn't cause reconsideration. --echo # EXPLAIN SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 5; let $start_time = `SELECT UNIX_TIMESTAMP(NOW(6))`; SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 5; SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%index_order_summary%plan_changed%false%') AS should_be_1 FROM information_schema.optimizer_trace; let $run_time = `SELECT UNIX_TIMESTAMP(NOW(6)) - $start_time`; let $query_was_slow = `SELECT $run_time > 0.1 AS query_was_slow`; if ($query_was_slow) { --echo # Run time exceeded limit! Time was: $run_time } if (!$query_was_slow) { --echo # Run time was as expected (fast, < 0.1s). } --echo --echo --echo --echo # --echo # Cleaning up. --echo # DROP TABLE `t`;