Description:
the cost of K1 is calculated as the same as primary key
the sample stack:
ha_innobase::read_time(index=4294967)
handler::read_cost(index=4294967)
Optimize_table_order::calculate_scan_cost(idx=0, best_ref=0x0, prefix_rowcount=1)
Optimize_table_order::best_access_path()
best_ref=0x0 in Optimize_table_order::calculate_scan_cost is because it it didn't
call find_best_ref()
in Optimize_table_order::best_access_path() function
tab->keyuse() is nullptr, best_ref is not find_best_ref, is 0X0
// Look for the best ref access if the storage engine supports index access.
if (tab->keyuse() != nullptr &&
(table->file->ha_table_flags() & HA_NO_INDEX_ACCESS) == 0)
best_ref =
find_best_ref(tab, remaining_tables, idx, prefix_rowcount,
&found_condition, &ref_depend_map, &used_key_parts);
in the function Optimize_table_order::calculate_scan_cost()
tab->ref().key is not set before ,its values is -1, that is 4294967 for uint.
trace_access_scan->add_alnum("access_type", "scan");
// Cost of scanning the table once
Cost_estimate scan_cost;
if (table->force_index && !best_ref) // index scan
scan_cost = table->file->read_cost(tab->ref().key, 1,
static_cast<double>(tab->records()));
else
scan_cost = table->file->table_scan_cost(); // table scan
const double single_scan_read_cost = scan_cost.total_cost();
How to repeat:
create table t1(a int primary key, b int, c text);
DELIMITER |;
CREATE PROCEDURE populate_t1(total_num INT)
BEGIN
DECLARE i int DEFAULT 1;
START TRANSACTION;
WHILE (i <= total_num) DO
INSERT INTO t1 VALUES (i, i, REPEAT(CONCAT('a', i),1000));
SET i = i + 1;
END WHILE;
COMMIT;
END|;
DELIMITER ;
CALL populate_t1(20000);
alter table t1 add index k1(b);
alter table t1 add index k2(a, b), add index k3(c(150));
analyze table t1;
## not choose index k1, choose table_scan instead;
mysql> explain select * from t1 force index(k1);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 11732 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Description: the cost of K1 is calculated as the same as primary key the sample stack: ha_innobase::read_time(index=4294967) handler::read_cost(index=4294967) Optimize_table_order::calculate_scan_cost(idx=0, best_ref=0x0, prefix_rowcount=1) Optimize_table_order::best_access_path() best_ref=0x0 in Optimize_table_order::calculate_scan_cost is because it it didn't call find_best_ref() in Optimize_table_order::best_access_path() function tab->keyuse() is nullptr, best_ref is not find_best_ref, is 0X0 // Look for the best ref access if the storage engine supports index access. if (tab->keyuse() != nullptr && (table->file->ha_table_flags() & HA_NO_INDEX_ACCESS) == 0) best_ref = find_best_ref(tab, remaining_tables, idx, prefix_rowcount, &found_condition, &ref_depend_map, &used_key_parts); in the function Optimize_table_order::calculate_scan_cost() tab->ref().key is not set before ,its values is -1, that is 4294967 for uint. trace_access_scan->add_alnum("access_type", "scan"); // Cost of scanning the table once Cost_estimate scan_cost; if (table->force_index && !best_ref) // index scan scan_cost = table->file->read_cost(tab->ref().key, 1, static_cast<double>(tab->records())); else scan_cost = table->file->table_scan_cost(); // table scan const double single_scan_read_cost = scan_cost.total_cost(); How to repeat: create table t1(a int primary key, b int, c text); DELIMITER |; CREATE PROCEDURE populate_t1(total_num INT) BEGIN DECLARE i int DEFAULT 1; START TRANSACTION; WHILE (i <= total_num) DO INSERT INTO t1 VALUES (i, i, REPEAT(CONCAT('a', i),1000)); SET i = i + 1; END WHILE; COMMIT; END|; DELIMITER ; CALL populate_t1(20000); alter table t1 add index k1(b); alter table t1 add index k2(a, b), add index k3(c(150)); analyze table t1; ## not choose index k1, choose table_scan instead; mysql> explain select * from t1 force index(k1); +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 11732 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+ 1 row in set, 1 warning (0.00 sec)