Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6
-
None
-
None
Description
Testcase to repeat:
create table t1 (a int); |
insert into t1 select 1000 from seq_1_to_1000; |
insert into t1 select 10 from seq_1_to_1000; |
insert into t1 select 10000 from seq_1_to_100; |
set analyze_sample_percentage=20; |
analyze table t1 persistent for all; |
So, we have 2100 records in table t1 and for each execution of
analyze table t1 persistent for all; |
we will get a new value for cardinality in mysql.table_stats. And very often this value will exceed 2100 and and sometimes more than 10% the actual number of records in the table.
E.g.:
MariaDB [mysqltest]> select * from mysql.table_stats where table_name='t1'; |
+-----------+------------+-------------+ |
| db_name | table_name | cardinality |
|
+-----------+------------+-------------+ |
| test | t1 | 2360 |
|
+-----------+------------+-------------+ |
 |
MariaDB [mysqltest]> explain extended select * from t1 where a between 1500 and 1600; |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2360 | 33.33 | Using where | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ |
Attachments
Issue Links
- relates to
-
MDEV-32472 Test histogram precision for ANALYZE, 2023
-
- Stalled
-