DATASET
set @@use_stat_tables=PREFERABLY;
|
CREATE TABLE t1 (i int, a VARCHAR(1000) DEFAULT "AAA")
|
PARTITION BY RANGE COLUMNS (a)(
|
PARTITION p0 VALUES LESS THAN ('g') COMMENT "p0",
|
PARTITION p1 VALUES LESS THAN ('m'),
|
PARTITION p2 VALUES LESS THAN ('t'),
|
PARTITION p3 VALUES LESS THAN ('w'));
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (MAXVALUE));
|
|
INSERT INTO t1 VALUES (1,REPEAT('a',100)),(2,REPEAT('v',200)),(3,REPEAT('r',300)),(4,NULL);
|
INSERT INTO t1 VALUES (5,REPEAT('k',500)),(6,'April'),(7,7),(8,""),(9,"M"),(10,DEFAULT);
|
|
DELETE FROM t1 where a="";
|
DELETE FROM t1 where a=(REPEAT('a',100));
|
DELETE FROM t1 where a like "%v";
|
ALTER TABLE t1 ANALYZE PARTITION p1;
|
Then I run:
Case 1
MariaDB [test]> set @@use_stat_tables=PREFERABLY;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m');
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | p2,p3,p4 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
Case 2
|
MariaDB [test]> set @@use_stat_tables=NEVER;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m');
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | p2,p3,p4 | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
The rows in case 1 return all records of the table, that is 7 in this case
while rows in case 2 return records that belong to the partitions p2,,p3 and p4.
Bug
Major
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
