|
Example: a table with 50K rows. All rows have a1=1. However, ha_innobase::records_in_range() will return about 25K to the optimizer.
This may cause a bad query plan. The impact of this is bigger in recent versions as they use E(rows) from the potential range accesses as a source of data about condition selectivity.
--source include/have_sequence.inc
|
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (id int PRIMARY KEY, a1 int, KEY (a1))engine=innodb;
|
INSERT INTO t1 SELECT seq FROM seq_1_to_50000;
|
|
MariaDB [test]> explain select * FROM t1 force index(a1) where a1=1;
|
+------+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
|
| 1 | SIMPLE | t1 | ref | a1 | a1 | 5 | const | 24485 | Using index |
|
+------+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
|
MariaDB [test]> select count(*) from t1 where a1=1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 50000 |
|
+----------+
|
I would expect the estimates to be more closer for such cases.
|