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

[Venkat V]

Any other suggestion to get the proof ?


I have this

mysql> explain partitions select * from ServerPerfStore where Date>='2010-09-17' and Date <= '2010-09-20'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ServerPerfStore
partitions: PART_20100818000000,PART_20100917000000,PART_20100918000000,PART_20100919000000,PART_20100920000000
type: ALL
possible_keys: date_idx
key: NULL
key_len: NULL
ref: NULL
rows: 8144
Extra: Using where
1 row in set (0.00 sec)

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

mysql>

mysql> select count(*) from ServerPerfStore \G
*************************** 1. row ***************************
count(*): 8144


mysql> select count(*) from ServerPerfStore where Date='2010-09-17' \G
*************************** 1. row ***************************
count(*): 399


rows : 8144 <- can i reduce the rows scan to 399?