revision-id: a9be8557c4f7fe52efd2c5231f95adb0bad8d80a (mariadb-10.0.36-12-ga9be8557c4f) parent(s): 880a22e5c4aa7239b3244ba1b83854955295e849 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-28 16:54:46 +0530 message: MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY The problem here is EITS statistics does not calculate statistics for the partitions of the table. So a temporary solution would be to not read EITS statistics for partitioned tables. --- mysql-test/r/partition.result | 27 +++++++++++++++++++++++++++ mysql-test/t/partition.test | 22 ++++++++++++++++++++++ sql/sql_statistics.cc | 6 ++++++ 3 files changed, 55 insertions(+) diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index c6669176b3d..638e05eadb9 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -2645,3 +2645,30 @@ Warnings: Note 1517 Duplicate partition name p2 DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# MDEV-17032: Estimates are higher for partitions of a table with @@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')); +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables=PREFERABLY; +ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (MAXVALUE)); +INSERT INTO t1 VALUES (3,REPEAT('r',300)),(4,NULL); +INSERT INTO t1 VALUES (5,REPEAT('k',500)),(6,'April'),(7,7),(9,"M"),(10,DEFAULT); +ALTER TABLE t1 ANALYZE PARTITION p1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +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 2 Using where +set @@use_stat_tables=NEVER; +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 2 Using where +set @use_stat_tables= @save_use_stat_tables; +drop table t1; diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 1c8cd0375d6..46758bc69b1 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -2897,3 +2897,25 @@ EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE t1; +--echo # +--echo # MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY +--echo # + +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')); +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables=PREFERABLY; +ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (MAXVALUE)); +INSERT INTO t1 VALUES (3,REPEAT('r',300)),(4,NULL); +INSERT INTO t1 VALUES (5,REPEAT('k',500)),(6,'April'),(7,7),(9,"M"),(10,DEFAULT); +ALTER TABLE t1 ANALYZE PARTITION p1; +EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m'); +set @@use_stat_tables=NEVER; +EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m'); +set @use_stat_tables= @save_use_stat_tables; +drop table t1; + diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index cb75a5c2176..33a12588434 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3589,6 +3589,12 @@ void set_statistics_for_table(THD *thd, TABLE *table) (use_stat_table_mode <= COMPLEMENTARY || !table->stats_is_read || read_stats->cardinality_is_null) ? table->file->stats.records : read_stats->cardinality; + + #ifdef WITH_PARTITION_STORAGE_ENGINE + if (table->part_info) + table->used_stat_records= table->file->stats.records; + #endif + KEY *key_info, *key_info_end; for (key_info= table->key_info, key_info_end= key_info+table->s->keys; key_info < key_info_end; key_info++)