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

[Venkat V]

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