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:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[17 Aug 2009 7:17] Mikiya Okuno
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.
[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)