[#MDEV-18949] Analyze show ref(const,const) when we are using index scan

create table t0 (a int);

INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);

CREATE TABLE t1 (

a int(11) DEFAULT NULL,

b int(11) DEFAULT NULL,

d int(11) DEFAULT NULL,

KEY a_d (a,d),

KEY a_b (a,b)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D;

MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 20000;

+------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref         | rows  | r_rows   | filtered | r_filtered | Extra       |

+------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+

|    1 | SIMPLE      | t1    | index | a_d,a_b       | a_b | 10      | const,const | 10266 | 10000.00 |     1.95 |       2.00 | Using where |

+------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+

CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (b));

INSERT INTO t1 SELECT seq%2, seq FROM seq_1_to_100;

MariaDB [test]> EXPLAIN SELECT a,b FROM t1 WHERE a=1 ORDER BY b LIMIT 5;

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | t1    | index | a             | b    | 5       | NULL |    7 | Using where |

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

MariaDB [test]> ANALYZE SELECT a,b FROM t1 WHERE a=1 ORDER BY b LIMIT 5;

+------+-------------+-------+-------+---------------+------+---------+-------+------+--------+----------+------------+-------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra       |

+------+-------------+-------+-------+---------------+------+---------+-------+------+--------+----------+------------+-------------+

|    1 | SIMPLE      | t1    | index | a             | b    | 5       | const |  100 |   9.00 |    69.00 |      55.56 | Using where |

+------+-------------+-------+-------+---------------+------+---------+-------+------+--------+----------+------------+-------------+

1 row in set (0.00 sec)

[MDEV-18949] Analyze show ref(const,const) when we are using index scan Created: 2019-03-16 Updated: 2023-11-10
Status:	Stalled
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.1, 10.2, 10.3, 10.4
Fix Version/s:	10.4