MySQL Forums
Forum List  »  Partitioning

Re: After partition also select is scanning all the partitions - need help
Posted by: Venkat V
Date: September 24, 2010 11:33PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: After partition also select is scanning all the partitions - need help
1801
September 24, 2010 11:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.