| Bug #46754 | 'rows' field doesn't reflect partition pruning | ||
|---|---|---|---|
| Submitted: | 17 Aug 2009 7:17 | Modified: | 8 Sep 2010 11:35 |
| Reporter: | Mikiya Okuno | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[18 Aug 2009 10:45]
Mattias Jonsson
More info is in bug#37532, and this is also documented behavior: As of MySQL 5.1.28, the rows column of EXPLAIN PARTITIONS output always displays the total number of records in the table. Previously, this was the number of matching rows. (Bug#35745) from http://dev.mysql.com/doc/refman/5.1/en/partitioning-info.html
[7 Sep 2009 9:20]
Mikael Ronström
This is really a feature request. Currently the partition handler returns all rows in the table when a table scan is to be performed. It sounds as a more correct answer to reply with only the rows in the partitions that will be scanned (so removing the partitions which are partition pruned). It should be very simple to implement by adding an if-statement in ha_partition::records() to look at if the partition is part of the scan. The only other user of records() is in SELECT COUNT(..) queries where it should also be correct to take this into account, actually it isn't likely to call this method in SELECT COUNT(..) unless there are no conditions on the table. So it's really a matter of deciding if we want to change the behaviour once more. The behaviour of this already changed in 5.1.28.
[5 Nov 2009 21:42]
Jason Shoulet
What about the row count using EXPLAIN SELECT? This shows total rows for me as well instead of the number of partitioned rows scanned. Is this the expected behavior?
[14 May 2010 0:37]
Jim Plush
I'm also experiencing this... I have a table loaded with 4 million rows, I then created 100 partitions confirmed with EXPLAIN PARTITIONS. When I do a select using the partitioned column and EXPLAIN it shows all the 4 million rows. I would expect to see the actual rows that would be scanned using this particular partition.
[26 Aug 2010 15:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116908 3489 Mattias Jonsson 2010-08-26 Bug#53806: Wrong estimates for range query in partitioned MyISAM table Bug#46754: 'rows' field doesn't reflect partition pruning The EXPLAIN's result in 'rows' field was evaluated to number of rows when the table was opened (not from the table cache) and only the partitions left after pruning was updated with its correct number of rows. The evaluation of the 'rows' field was using handler::records() which is a potentially expensive call, and ignores the partitioning pruning. The fix was to use the handlers stats.records after updating it with ::info(HA_STATUS_VARIABLE) instead. @ mysql-test/r/partition_pruning.result updated result @ mysql-test/t/partition_pruning.test Added test. @ sql/sql_select.cc Use ::info + stats.records instead of ::records().
[27 Aug 2010 8:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116963 3490 Mattias Jonsson 2010-08-27 Bug#53806: Wrong estimates for range query in partitioned MyISAM table Bug#46754: 'rows' field doesn't reflect partition pruning Update of test results after fixing the above bugs. (fix in separate commit). @ mysql-test/r/partition.result Updated test result after fixing bugs 46754 and 53806 @ mysql-test/r/partition_hash.result Updated test result after fixing bugs 46754 and 53806 @ mysql-test/r/partition_innodb.result Updated test result after fixing bugs 46754 and 53806 @ mysql-test/r/partition_range.result Updated test result after fixing bugs 46754 and 53806 @ mysql-test/suite/parts/r/partition_alter3_innodb.result Updated test result after fixing bugs 46754 and 53806 @ mysql-test/suite/parts/r/partition_alter3_myisam.result Updated test result after fixing bugs 46754 and 53806
[14 Sep 2010 9:26]
Mattias Jonsson
closed as duplicate of bug#53806.
[28 Sep 2010 15:39]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)
[28 Sep 2010 15:42]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)
[28 Sep 2010 15:44]
Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)
[1 Nov 2010 19:01]
Bugs System
Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)
Description: Some storage engines doesn't take partitioning pruning into account for 'rows' fields in EXPLAIN output. Such storage engines are MyISAM and ARCHIVE, which they keep row counts. How to repeat: 1. Create a partitioned table. mysql> CREATE TABLE t1 (a INT UNSIGNED) ENGINE MYISAM PARTITION BY HASH(a) PARTITIONS 16; 2. Fill it with some test data. delimiter // create procedure p1() begin set @x:=0; repeat insert into t1 (a) values(@x); set @x:=@x+1; until @x = 1024 end repeat; end;// delimiter ; mysql> call p1; This procedure will insert 1024 rows. 3. See the explain output. explain partitions select * from t15 where a=100\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p4 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1024 Extra: Using where 1 row in set (0.00 sec) The partition in question is p4, and it should have 64 rows, but the rows field shows the number of rows in the entire table, 1024. Suggested fix: Optimizer should fetch number of rows from each partition instead of table if the storage engine keeps exact row count.