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

[Venkat V]

Hi

Understood. I took the date where the partition exists and used a fresh query.

Condition of the query is mytime >= '2010-09-04 00:00:00' and mytime <= '2010-09-06 23:00:00';

in non partition table:

mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 0 |
+-------------------+-------+
1 row in set (0.00 sec)


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


After that when i execute the query --> it took 8066 rows in set (1 min 46.29 sec)

mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 8066 |
+-------------------+-------+
1 row in set (0.00 sec)

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

But in partitioned table


mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 0 |
+-------------------+-------+
1 row in set (0.00 sec)

After that when i execute the query --> it took 8066 rows in set (0.04 sec)

mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 8066 |
+-------------------+-------+
1 row in set (0.00 sec)


==========
Here i could see the difference only in speed. But still number of scan rows are same on both. I found one more weird thing too.

ysql> explain partitions select * from mytable where mytime >= '2010-09-04 00:00:00' and mytime<= '2010-09-06 23:00:00' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
partitions: PART_20100817000000,PART_20100904000000,PART_20100905000000,PART_20100906000000
type: range
possible_keys: PRIMARY,mytime
key: mytime
key_len: 8
ref: NULL
rows: 8060
Extra: Using where
1 row in set (0.00 sec)

mysql>


mysql> explain partitions select * from mytable where mytime >= '2010-09-04 01:00:00' and mytime<= '2010-09-06 23:00:00' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
partitions: PART_20100817000000,PART_20100904000000,PART_20100905000000,PART_20100906000000
type: range
possible_keys: PRIMARY,mytime
key: mytime
key_len: 8
ref: NULL
rows: 7965
Extra: Using where
1 row in set (0.00 sec)

mysql>


If we select data from > 00:00:00 also, why the partition PART_20100817000000 is selected ?. Is that the reason, we are scanning all the rows ?

The below output from non partition table in my another replicated db.

mysql> explain partitions select * from mytable where mytime >= '2010-09-04 01:00:00' and mytime <= '2010-09-06 23:00:00' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 3067
Extra: Using where
1 row in set (0.00 sec)

mysql>

Thanks