MySQL Forums
Forum List  »  Partitioning

After partition also select is scanning all the partitions - need help
Posted by: Venkat V
Date: September 13, 2010 08:50AM

Hi

I recently created the RANGE partition based on Date ( type : date, format : 0000-00-00 ).


mysql> select count(*) from mytable;
+----------+
| count(*) |
+----------+
| 5379 |
+----------+
1 row in set (0.00 sec)

mysql> explain partitions select * from mytable where Date='2010-09-10' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
partitions: PART_20100910000000
type: ALL
possible_keys: date_idx
key: NULL
key_len: NULL
ref: NULL
rows: 5379
Extra: Using where
1 row in set (0.00 sec)

mysql>

Even though it is locating the partition, scanning all the rows.

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

mysql> explain partitions select * from mytable \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
partitions: PART_20100801000000,PART_20100818000000,PART_20100819000000,PART_20100820000000,PART_20100821000000,PART_20100822000000,PART_20100823000000,PART_20100824000000,PART_20100825000000,PART_20100826000000,PART_20100827000000,PART_20100828000000,PART_20100829000000,PART_20100830000000,PART_20100831000000,PART_20100901000000,PART_20100902000000,PART_20100903000000,PART_20100904000000,PART_20100905000000,PART_20100906000000,PART_20100907000000,PART_20100908000000,PART_20100909000000,PART_20100910000000,PART_20100911000000,PART_20100912000000,PART_20100913000000,PART_20100914000000,PART_20100915000000,PART_20100916000000,PART_20100917000000,PART_20100918000000,PART_20100919000000,PART_20100920000000,PART_20100921000000,PART_20100922000000,PART_20100923000000,PART_20100924000000,PART_20100925000000,PART_20100926000000,PART_20100927000000,PART_20100928000000,PART_20100929000000,PART_20100930000000
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5379
Extra:
1 row in set (0.00 sec)

mysql>

How to reduce the rows scan and improve the performance. Please correct me where i am wrong.

Thanks
venkat

Options: ReplyQuote


Subject
Views
Written By
Posted
After partition also select is scanning all the partitions - need help
3024
September 13, 2010 08:50AM


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.