Re: After partition also select is scanning all the partitions - need help

[Venkat V]

FROM Partitioned table

mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 14224 |
| Handler_read_rnd_next | 227626 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 14252 |
+----------------------------+--------+
15 rows in set (0.00 sec)

mysql>


mysql> select * from mytable where mytime >= '2010-09-04 00:00:00' and mytime <= '2010-09-07 00:00:00';
8162 rows in set (0.08 sec)


mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 5 |
| Handler_read_next | 8162 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 14224 |
| Handler_read_rnd_next | 227631 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 14256 |
+----------------------------+--------+
15 rows in set (0.00 sec)

==========================================================================================

Non Partitioned Table

mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 106933 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 14224 |
| Handler_read_rnd_next | 14402 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 14260 |
+----------------------------+--------+
15 rows in set (0.00 sec)

mysql> select * from mytable where mytime >= '2010-09-04 00:00:00' and mytime <= '2010-09-07 00:00:00';
8162 rows in set (0.08 sec)


mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_next | 115095 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 14224 |
| Handler_read_rnd_next | 14402 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 14260 |
+----------------------------+--------+
15 rows in set (0.00 sec)
==========================================================================================