Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.4, 11.5(EOL)
-
None
Description
Note: Possibly earlier versions are also affected, but the query in the test case only uses range access on 10.6+.
At least USER_STATISTICS and INDEX_STATISTICS ignore certain queries on partitioned tables.
--source include/have_partition.inc
|
|
|
SET @userstat.save= @@userstat; |
set global userstat= 1; |
|
|
create or replace table t (a int, key(a)) engine=MyISAM partition by hash(a) partitions 3; |
insert into t values (1),(2),(3),(4),(5); |
|
|
flush index_statistics;
|
flush table_statistics;
|
select * from t where a in (1,3); |
show index_statistics;
|
show table_statistics;
|
explain select * from t where a in (1,3); |
|
|
alter table t remove partitioning; |
|
|
flush index_statistics;
|
flush table_statistics;
|
select * from t where a in (1,3); |
show index_statistics;
|
show table_statistics;
|
explain select * from t where a in (1,3); |
|
|
# Cleanup
|
drop table t; |
set global userstat= @userstat.save; |
On a partitioned table:
|
10.6 8dda6027019c93fb58d34095cb15908cdfa209e6 |
MariaDB [test]> show index_statistics;
|
Empty set (0.000 sec) |
|
|
MariaDB [test]> show table_statistics;
|
Empty set (0.000 sec) |
|
|
MariaDB [test]> explain select * from t where a in (1,3); |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| 1 | SIMPLE | t | range | a | a | 5 | NULL | 3 | Using where; Using index | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
After removing partitioning:
MariaDB [test]> show index_statistics;
|
+--------------+------------+------------+-----------+---------+ |
| Table_schema | Table_name | Index_name | Rows_read | Queries |
|
+--------------+------------+------------+-----------+---------+ |
| test | t | a | 2 | 1 |
|
+--------------+------------+------------+-----------+---------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> show table_statistics;
|
+--------------+------------+-----------+--------------+-------------------------+ |
| Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes |
|
+--------------+------------+-----------+--------------+-------------------------+ |
| test | t | 2 | 0 | 0 |
|
+--------------+------------+-----------+--------------+-------------------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> explain select * from t where a in (1,3); |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| 1 | SIMPLE | t | range | a | a | 5 | NULL | 2 | Using where; Using index | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
1 row in set (0.001 sec) |