Description:
When using a CASE WHEN THEN ELSE END condition to build the Where clause of a query, MySQL will search ALL of that tables partitions instead of just the ones the Where clause should be limiting it to.
How to repeat:
mysql> show global variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.19 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.19 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | debian6.0 |
+-------------------------+------------------------------+
7 rows in set (0.08 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN PARTITIONS SELECT COUNT( * ) AS Count
FROM test.tblDataSentSMS du
JOIN test.vwUnits un ON ( du.ESN = un.ESN
AND un.CarrierName = 'Cingular' )
WHERE
CASE
WHEN DATE_FORMAT( NOW( ) , '%e' ) <=8
THEN du.DateSent
BETWEEN DATE_SUB( DATE_FORMAT( NOW( ) , '%Y-%m-9' ) , INTERVAL 1
MONTH )
AND DATE_SUB( DATE_FORMAT( NOW( ) , '%Y-%m-%d' ) , INTERVAL 1
DAY )
ELSE du.DateSent
BETWEEN DATE_FORMAT( NOW( ) , '%Y-%m-09' )
AND DATE_SUB( DATE_FORMAT( NOW( ) , '%Y-%m-%d' ) , INTERVAL 1
DAY )
END\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tblCellularCarriers
partitions: NULL
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 50
ref: const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: du
partitions: P21Nov2012,P22Nov2012,P23Nov2012,P24Nov2012,P25Nov2012,P26Nov2012,P27Nov2012,P28Nov2012,P29Nov2012,P30Nov2012,P01Dec2012,P02Dec2012,P03Dec2012,P04Dec2012,P05Dec2012,P06Dec2012,P07Dec2012,P08Dec2012,P09Dec2012,P10Dec2012,P11Dec2012,P12Dec2012,P13Dec2012,P14Dec2012,P15Dec2012,P16Dec2012,P17Dec2012,P18Dec2012,P19Dec2012,P20Dec2012,P21Dec2012,P22Dec2012,P23Dec2012,P24Dec2012,P25Dec2012,P26Dec2012,P27Dec2012,P28Dec2012,P29Dec2012,P30Dec2012,P31Dec2012,P01Jan2013,P02Jan2013,P03Jan2013,P04Jan2013,P05Jan2013,P06Jan2013,P07Jan2013,P08Jan2013,P09Jan2013,P10Jan2013,P11Jan2013,P12Jan2013,P13Jan2013,P14Jan2013,P15Jan2013,P16Jan2013,P17Jan2013,P18Jan2013,P19Jan2013,P20Jan2013,P21Jan2013,P22Jan2013,P23Jan2013,P24Jan2013,P25Jan2013,P26Jan2013,P27Jan2013,P28Jan2013,P29Jan2013,P30Jan2013,P31Jan2013,P01Feb2013,P02Feb2013,P03Feb2013,P04Feb2013,P05Feb2013,P06Feb2013,P07Feb2013,P08Feb2013,P09Feb2013,P10Feb2013,P11Feb2013,P12Feb2013,P13Feb2013,P14Feb2013,P15Feb2013,P16Feb2013,P17Feb2013,P18Feb2013,P19Feb2013,P20Feb2013,P21Feb2013,P22Feb2013,P23Feb2013,P24Feb2013,P25Feb2013,P26Feb2013,P27Feb2013,P28Feb2013,P01Mar2013,P02Mar2013,P03Mar2013,P04Mar2013,P05Mar2013,P06Mar2013,P07Mar2013,P08Mar2013,P09Mar2013,P10Mar2013,P11Mar2013,P12Mar2013,P13Mar2013,P14Mar2013,P15Mar2013,P16Mar2013,P17Mar2013,P18Mar2013,P19Mar2013,P20Mar2013,P21Mar2013,P22Mar2013,P23Mar2013,P24Mar2013,P25Mar2013,P26Mar2013,P27Mar2013,P28Mar2013,P29Mar2013,P30Mar2013,P31Mar2013,P01Apr2013,P02Apr2013,P03Apr2013,P04Apr2013,P05Apr2013,P06Apr2013,P07Apr2013,P08Apr2013,P09Apr2013,P10Apr2013,P11Apr2013,P12Apr2013,P13Apr2013,P14Apr2013,P15Apr2013,P16Apr2013,P17Apr2013,P18Apr2013,P19Apr2013,P20Apr2013,P21Apr2013,P22Apr2013,P23Apr2013,P24Apr2013,P25Apr2013,P26Apr2013,P27Apr2013,P28Apr2013,P29Apr2013,P30Apr2013,P01May2013,P02May2013,P03May2013,P04May2013,P05May2013,P06May2013,P07May2013,P08May2013,P09May2013,P10May2013,P11May2013,P12May2013,P13May2013,P14May2013,P15May2013,P16May2013,P17May2013,P18May2013,P19May2013,P20May2013,P21May2013,P22May2013,P23May2013,P24May2013,P25May2013,P26May2013,P27May2013,P28May2013,P29May2013,P30May2013,P31May2013,P01Jun2013,P02Jun2013,P03Jun2013,P04Jun2013,P05Jun2013,P06Jun2013,P07Jun2013,P08Jun2013,P09Jun2013,P10Jun2013,P11Jun2013,P12Jun2013,P13Jun2013,P14Jun2013,P15Jun2013,P16Jun2013,P17Jun2013,P18Jun2013,P19Jun2013,P20Jun2013,P21Jun2013,P22Jun2013,P23Jun2013,P24Jun2013,P25Jun2013,P26Jun2013,P27Jun2013,P28Jun2013,P29Jun2013,P30Jun2013,P01Jul2013,P02Jul2013,P03Jul2013,P04Jul2013,P05Jul2013,P06Jul2013,P07Jul2013,P08Jul2013,P09Jul2013,P10Jul2013,P11Jul2013,P12Jul2013,P13Jul2013,P14Jul2013,P15Jul2013,P16Jul2013,P17Jul2013,P18Jul2013,P19Jul2013,P20Jul2013,P21Jul2013,P22Jul2013,P23Jul2013,P24Jul2013,P25Jul2013,P26Jul2013,P27Jul2013,P28Jul2013,P29Jul2013,P30Jul2013,P31Jul2013,P01Aug2013,P02Aug2013,P03Aug2013,P04Aug2013,P05Aug2013,P06Aug2013,P07Aug2013,P08Aug2013,P09Aug2013,P10Aug2013,P11Aug2013,P12Aug2013,P13Aug2013,P14Aug2013,P15Aug2013,P16Aug2013,P17Aug2013,P18Aug2013,P19Aug2013,P20Aug2013,P21Aug2013,P22Aug2013,P23Aug2013,P24Aug2013,P25Aug2013,P26Aug2013,P27Aug2013,P28Aug2013,P29Aug2013,P30Aug2013,P31Aug2013,P01Sep2013,P02Sep2013,P03Sep2013,P04Sep2013,P05Sep2013,P06Sep2013,P07Sep2013,P08Sep2013,P09Sep2013,P10Sep2013,P11Sep2013,P12Sep2013,P13Sep2013,P14Sep2013,P15Sep2013,P16Sep2013,P17Sep2013,P18Sep2013,P19Sep2013,P20Sep2013,P21Sep2013,P22Sep2013,P23Sep2013,P24Sep2013,P25Sep2013,P26Sep2013,P27Sep2013,P28Sep2013,P29Sep2013,P30Sep2013,P01Oct2013,P02Oct2013,P03Oct2013,P04Oct2013,P05Oct2013,P06Oct2013,P07Oct2013,P08Oct2013,P09Oct2013,P10Oct2013,P11Oct2013,P12Oct2013,P13Oct2013,P14Oct2013,P15Oct2013,P16Oct2013,P17Oct2013,P18Oct2013,P19Oct2013,P20Oct2013,P21Oct2013,P22Oct2013,P23Oct2013,P24Oct2013,P25Oct2013,P26Oct2013,P27Oct2013,P28Oct2013,P29Oct2013,P30Oct2013,P31Oct2013,P01Nov2013,P02Nov2013,P03Nov2013,P04Nov2013,P05Nov2013,P06Nov2013,P07Nov2013,P08Nov2013,P09Nov2013,P10Nov2013,P11Nov2013,P12Nov2013,P13Nov2013,P14Nov2013,P15Nov2013,P16Nov2013,P17Nov2013,P18Nov2013,P19Nov2013,P20Nov2013,P21Nov2013,P22Nov2013,P23Nov2013,P24Nov2013,P25Nov2013,P26Nov2013,P27Nov2013,P28Nov2013,P29Nov2013,P30Nov2013,P01Dec2013,P02Dec2013,P03Dec2013,P04Dec2013,P05Dec2013,P06Dec2013,P07Dec2013,P08Dec2013,P09Dec2013,P10Dec2013,P11Dec2013,P12Dec2013,P13Dec2013,P14Dec2013,P15Dec2013,P16Dec2013,P17Dec2013,P18Dec2013,P19Dec2013,P20Dec2013,P21Dec2013,P22Dec2013,P23Dec2013,P24Dec2013,P25Dec2013,P26Dec2013,P27Dec2013,P28Dec2013,P29Dec2013,P30Dec2013,P31Dec2013,P01Jan2014,P02Jan2014,P03Jan2014,P04Jan2014,P05Jan2014,P06Jan2014,P07Jan2014,P08Jan2014,P09Jan2014,P10Jan2014,P11Jan2014,P12Jan2014,P13Jan2014,P14Jan2014,P15Jan2014,P16Jan2014,P17Jan2014,P18Jan2014,P19Jan2014,P20Jan2014,P21Jan2014,P22Jan2014,P23Jan2014,P24Jan2014,P25Jan2014,P26Jan2014,P27Jan2014,P28Jan2014,P29Jan2014,P30Jan2014,P31Jan2014,P01Feb2014,P02Feb2014,P03Feb2014,P04Feb2014,P05Feb2014,P06Feb2014,P07Feb2014,P08Feb2014,P09Feb2014,P10Feb2014,P11Feb2014,P12Feb2014,P13Feb2014,P14Feb2014,P15Feb2014,P16Feb2014,P17Feb2014,P18Feb2014,P19Feb2014,P20Feb2014,P21Feb2014,P22Feb2014,P23Feb2014,P24Feb2014,P25Feb2014,P26Feb2014,P27Feb2014,P28Feb2014,P01Mar2014,P02Mar2014,P03Mar2014,P04Mar2014,P05Mar2014,P06Mar2014,P07Mar2014,P08Mar2014,P09Mar2014,P10Mar2014,P11Mar2014,P12Mar2014,P13Mar2014,P14Mar2014,P15Mar2014,P16Mar2014,P17Mar2014,P18Mar2014,P19Mar2014,P20Mar2014,P21Mar2014,P22Mar2014,P23Mar2014,P24Mar2014,P25Mar2014,P26Mar2014,P27Mar2014,P28Mar2014,P29Mar2014,P30Mar2014,P31Mar2014,P01Apr2014,P02Apr2014,P03Apr2014,P04Apr2014,P05Apr2014,P06Apr2014,P07Apr2014,P08Apr2014,P09Apr2014,P10Apr2014,P11Apr2014,P12Apr2014,P13Apr2014,P14Apr2014,P15Apr2014,P16Apr2014,P17Apr2014,P18Apr2014,P19Apr2014,P20Apr2014,P21Apr2014,P22Apr2014,P23Apr2014,P24Apr2014,P25Apr2014,P26Apr2014,P27Apr2014,P28Apr2014,P29Apr2014,P30Apr2014,P01May2014,P02May2014,P03May2014,P04May2014,P05May2014,P06May2014,P07May2014,P08May2014,P09May2014,P10May2014,P11May2014,P12May2014,P13May2014,P14May2014,P15May2014,P16May2014,P17May2014,P18May2014,P19May2014,P20May2014,P21May2014,P22May2014,P23May2014,P24May2014,P25May2014,P26May2014,P27May2014,P28May2014,P29May2014,P30May2014,P31May2014,P01Jun2014,P02Jun2014,P03Jun2014,P04Jun2014,P05Jun2014,P06Jun2014,P07Jun2014,P08Jun2014,P09Jun2014,P10Jun2014,P11Jun2014,P12Jun2014,P13Jun2014,P14Jun2014,P15Jun2014,P16Jun2014,P17Jun2014,P18Jun2014,P19Jun2014,P20Jun2014,P21Jun2014,P22Jun2014,P23Jun2014,P24Jun2014,P25Jun2014,P26Jun2014,P27Jun2014,P28Jun2014,P29Jun2014,P30Jun2014,P01Jul2014,P02Jul2014,P03Jul2014,P04Jul2014,P05Jul2014,P06Jul2014,P07Jul2014,P08Jul2014,P09Jul2014,P10Jul2014,P11Jul2014,P12Jul2014,P13Jul2014,P14Jul2014,P15Jul2014,P16Jul2014,P17Jul2014,P18Jul2014,P19Jul2014,P20Jul2014,P21Jul2014,P22Jul2014,P23Jul2014,P24Jul2014,P25Jul2014,P26Jul2014,P27Jul2014,P28Jul2014,P29Jul2014,P30Jul2014,P31Jul2014,P01Aug2014,P02Aug2014,P03Aug2014,P04Aug2014,P05Aug2014,P06Aug2014,P07Aug2014,P08Aug2014,P09Aug2014,P10Aug2014,P11Aug2014,P12Aug2014,P13Aug2014,P14Aug2014,P15Aug2014,P16Aug2014,P17Aug2014,P18Aug2014,P19Aug2014,P20Aug2014,P21Aug2014,P22Aug2014,P23Aug2014,P24Aug2014,P25Aug2014,P26Aug2014,P27Aug2014,P28Aug2014,P29Aug2014,P30Aug2014,P31Aug2014,P01Sep2014,P02Sep2014,P03Sep2014,P04Sep2014,P05Sep2014,P06Sep2014,P07Sep2014,P08Sep2014,P09Sep2014,P10Sep2014,P11Sep2014,P12Sep2014,P13Sep2014,P14Sep2014,P15Sep2014,P16Sep2014,P17Sep2014,P18Sep2014,P19Sep2014,P20Sep2014,P21Sep2014,P22Sep2014,P23Sep2014,P24Sep2014,P25Sep2014,P26Sep2014,P27Sep2014,P28Sep2014,P29Sep2014,P30Sep2014,P01Oct2014,P02Oct2014,P03Oct2014,P04Oct2014,P05Oct2014,P06Oct2014,P07Oct2014,P08Oct2014,P09Oct2014,P10Oct2014,P11Oct2014,P12Oct2014,P13Oct2014,P14Oct2014,P15Oct2014,P16Oct2014,P17Oct2014,P18Oct2014,P19Oct2014,P20Oct2014,P21Oct2014,P22Oct2014,P23Oct2014,P24Oct2014,P25Oct2014,P26Oct2014,P27Oct2014,P28Oct2014,P29Oct2014,P30Oct2014,P31Oct2014,P01Nov2014,P02Nov2014,P03Nov2014,P04Nov2014,P05Nov2014,P06Nov2014,P07Nov2014,P08Nov2014,P09Nov2014,P10Nov2014,P11Nov2014,P12Nov2014,P13Nov2014,P14Nov2014,P15Nov2014,P16Nov2014,P17Nov2014,P18Nov2014,P19Nov2014,P20Nov2014,P21Nov2014,P22Nov2014,P23Nov2014,P24Nov2014,P25Nov2014,P26Nov2014,P27Nov2014,P28Nov2014,P29Nov2014,P30Nov2014,P01Dec2014,P02Dec2014,P03Dec2014,P04Dec2014,P05Dec2014,P06Dec2014,P07Dec2014,P08Dec2014,P09Dec2014,P10Dec2014,P11Dec2014,P12Dec2014,P13Dec2014,P14Dec2014,P15Dec2014,P16Dec2014,P17Dec2014,P18Dec2014,P19Dec2014,P20Dec2014,P21Dec2014,P22Dec2014,P23Dec2014,P24Dec2014,P25Dec2014,P26Dec2014,P27Dec2014,P28Dec2014,P29Dec2014,P30Dec2014,P31Dec2014,P01Jan2015,P02Jan2015,P03Jan2015,P04Jan2015,P05Jan2015,P06Jan2015,P07Jan2015,P08Jan2015,P09Jan2015,P10Jan2015,P11Jan2015,P12Jan2015,P13Jan2015,P14Jan2015,P15Jan2015,P16Jan2015,P17Jan2015,P18Jan2015,P19Jan2015,P20Jan2015,P21Jan2015,P22Jan2015,P23Jan2015,P24Jan2015,P25Jan2015,P26Jan2015,P27Jan2015,P28Jan2015,P29Jan2015,P30Jan2015,P31Jan2015,P01Feb2015,P02Feb2015,P03Feb2015,P04Feb2015,P05Feb2015,P06Feb2015,P07Feb2015,P08Feb2015,P09Feb2015,P10Feb2015,P11Feb2015,P12Feb2015,P13Feb2015,P14Feb2015,P15Feb2015,P16Feb2015,P17Feb2015,P18Feb2015,P19Feb2015,P20Feb2015,P21Feb2015,P22Feb2015,P23Feb2015,P24Feb2015,P25Feb2015,P26Feb2015,P27Feb2015,P28Feb2015,P01Mar2015,P02Mar2015,P03Mar2015,P04Mar2015,P05Mar2015,P06Mar2015,P07Mar2015,P08Mar2015,P09Mar2015,P10Mar2015,P11Mar2015,P12Mar2015,P13Mar2015,P14Mar2015,P15Mar2015,P16Mar2015,P17Mar2015,P18Mar2015,P19Mar2015,P20Mar2015,P21Mar2015,P22Mar2015,P23Mar2015,P24Mar2015,P25Mar2015,P26Mar2015,P27Mar2015,P28Mar2015,P29Mar2015,P30Mar2015,P31Mar2015,P01Apr2015,PMaxValue
type: index
possible_keys: ESN
key: ESN
key_len: 21
ref: NULL
rows: 1193
Extra: Using where; Using index; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: tblUnits
partitions: NULL
type: ref
possible_keys: ESN
key: ESN
key_len: 16
ref: test.du.ESN
rows: 1
Extra: Using index condition; Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: tblModelPrefixes
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6
Extra: Using where; Using join buffer (Block Nested Loop)
4 rows in set (0.04 sec)
If I reduce the above query to the case which will return based off of the given condition I get this:
mysql [localhost] {msandbox} (test) > EXPLAIN partitions (SELECT count(*) as Count FROM test.tblDataSentSMS du
JOIN test.vwUnits un on (du.ESN = un.ESN and un.CarrierName = 'Cingular')
WHERE du.DateSent Between DATE_SUB(date_format(now(), '%Y-%m-9'), interval 1 MONTH) AND DATE_SUB(date_format(now(), '%Y-%m-%d'), interval 1 day))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tblCellularCarriers
partitions: NULL
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 50
ref: const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: du
partitions: P21Nov2012,P10May2014,P11May2014,P12May2014,P13May2014,P14May2014,P15May2014,P16May2014,P17May2014,P18May2014,P19May2014,P20May2014,P21May2014,P22May2014,P23May2014,P24May2014,P25May2014,P26May2014,P27May2014,P28May2014,P29May2014,P30May2014,P31May2014,P01Jun2014,P02Jun2014,P03Jun2014,P04Jun2014,P05Jun2014,P06Jun2014,P07Jun2014,P08Jun2014,P09Jun2014,P10Jun2014,P11Jun2014,P12Jun2014,P13Jun2014,P14Jun2014,P15Jun2014,P16Jun2014,P17Jun2014,P18Jun2014,P19Jun2014
type: index
possible_keys: ESN
key: ESN
key_len: 21
ref: NULL
rows: 372
Extra: Using where; Using index; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: tblUnits
partitions: NULL
type: ref
possible_keys: ESN
key: ESN
key_len: 16
ref: test.du.ESN
rows: 1
Extra: Using index condition; Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: tblModelPrefixes
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6
Extra: Using where; Using join buffer (Block Nested Loop)
4 rows in set (0.10 sec)
You can see that ONLY the partitions that matter are scanned. But I would expect the first query to do the same thing.
Description: When using a CASE WHEN THEN ELSE END condition to build the Where clause of a query, MySQL will search ALL of that tables partitions instead of just the ones the Where clause should be limiting it to. How to repeat: mysql> show global variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.6.19 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.19 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | debian6.0 | +-------------------------+------------------------------+ 7 rows in set (0.08 sec) mysql [localhost] {msandbox} (test) > EXPLAIN PARTITIONS SELECT COUNT( * ) AS Count FROM test.tblDataSentSMS du JOIN test.vwUnits un ON ( du.ESN = un.ESN AND un.CarrierName = 'Cingular' ) WHERE CASE WHEN DATE_FORMAT( NOW( ) , '%e' ) <=8 THEN du.DateSent BETWEEN DATE_SUB( DATE_FORMAT( NOW( ) , '%Y-%m-9' ) , INTERVAL 1 MONTH ) AND DATE_SUB( DATE_FORMAT( NOW( ) , '%Y-%m-%d' ) , INTERVAL 1 DAY ) ELSE du.DateSent BETWEEN DATE_FORMAT( NOW( ) , '%Y-%m-09' ) AND DATE_SUB( DATE_FORMAT( NOW( ) , '%Y-%m-%d' ) , INTERVAL 1 DAY ) END\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tblCellularCarriers partitions: NULL type: ref possible_keys: PRIMARY key: PRIMARY key_len: 50 ref: const rows: 1 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: du partitions: P21Nov2012,P22Nov2012,P23Nov2012,P24Nov2012,P25Nov2012,P26Nov2012,P27Nov2012,P28Nov2012,P29Nov2012,P30Nov2012,P01Dec2012,P02Dec2012,P03Dec2012,P04Dec2012,P05Dec2012,P06Dec2012,P07Dec2012,P08Dec2012,P09Dec2012,P10Dec2012,P11Dec2012,P12Dec2012,P13Dec2012,P14Dec2012,P15Dec2012,P16Dec2012,P17Dec2012,P18Dec2012,P19Dec2012,P20Dec2012,P21Dec2012,P22Dec2012,P23Dec2012,P24Dec2012,P25Dec2012,P26Dec2012,P27Dec2012,P28Dec2012,P29Dec2012,P30Dec2012,P31Dec2012,P01Jan2013,P02Jan2013,P03Jan2013,P04Jan2013,P05Jan2013,P06Jan2013,P07Jan2013,P08Jan2013,P09Jan2013,P10Jan2013,P11Jan2013,P12Jan2013,P13Jan2013,P14Jan2013,P15Jan2013,P16Jan2013,P17Jan2013,P18Jan2013,P19Jan2013,P20Jan2013,P21Jan2013,P22Jan2013,P23Jan2013,P24Jan2013,P25Jan2013,P26Jan2013,P27Jan2013,P28Jan2013,P29Jan2013,P30Jan2013,P31Jan2013,P01Feb2013,P02Feb2013,P03Feb2013,P04Feb2013,P05Feb2013,P06Feb2013,P07Feb2013,P08Feb2013,P09Feb2013,P10Feb2013,P11Feb2013,P12Feb2013,P13Feb2013,P14Feb2013,P15Feb2013,P16Feb2013,P17Feb2013,P18Feb2013,P19Feb2013,P20Feb2013,P21Feb2013,P22Feb2013,P23Feb2013,P24Feb2013,P25Feb2013,P26Feb2013,P27Feb2013,P28Feb2013,P01Mar2013,P02Mar2013,P03Mar2013,P04Mar2013,P05Mar2013,P06Mar2013,P07Mar2013,P08Mar2013,P09Mar2013,P10Mar2013,P11Mar2013,P12Mar2013,P13Mar2013,P14Mar2013,P15Mar2013,P16Mar2013,P17Mar2013,P18Mar2013,P19Mar2013,P20Mar2013,P21Mar2013,P22Mar2013,P23Mar2013,P24Mar2013,P25Mar2013,P26Mar2013,P27Mar2013,P28Mar2013,P29Mar2013,P30Mar2013,P31Mar2013,P01Apr2013,P02Apr2013,P03Apr2013,P04Apr2013,P05Apr2013,P06Apr2013,P07Apr2013,P08Apr2013,P09Apr2013,P10Apr2013,P11Apr2013,P12Apr2013,P13Apr2013,P14Apr2013,P15Apr2013,P16Apr2013,P17Apr2013,P18Apr2013,P19Apr2013,P20Apr2013,P21Apr2013,P22Apr2013,P23Apr2013,P24Apr2013,P25Apr2013,P26Apr2013,P27Apr2013,P28Apr2013,P29Apr2013,P30Apr2013,P01May2013,P02May2013,P03May2013,P04May2013,P05May2013,P06May2013,P07May2013,P08May2013,P09May2013,P10May2013,P11May2013,P12May2013,P13May2013,P14May2013,P15May2013,P16May2013,P17May2013,P18May2013,P19May2013,P20May2013,P21May2013,P22May2013,P23May2013,P24May2013,P25May2013,P26May2013,P27May2013,P28May2013,P29May2013,P30May2013,P31May2013,P01Jun2013,P02Jun2013,P03Jun2013,P04Jun2013,P05Jun2013,P06Jun2013,P07Jun2013,P08Jun2013,P09Jun2013,P10Jun2013,P11Jun2013,P12Jun2013,P13Jun2013,P14Jun2013,P15Jun2013,P16Jun2013,P17Jun2013,P18Jun2013,P19Jun2013,P20Jun2013,P21Jun2013,P22Jun2013,P23Jun2013,P24Jun2013,P25Jun2013,P26Jun2013,P27Jun2013,P28Jun2013,P29Jun2013,P30Jun2013,P01Jul2013,P02Jul2013,P03Jul2013,P04Jul2013,P05Jul2013,P06Jul2013,P07Jul2013,P08Jul2013,P09Jul2013,P10Jul2013,P11Jul2013,P12Jul2013,P13Jul2013,P14Jul2013,P15Jul2013,P16Jul2013,P17Jul2013,P18Jul2013,P19Jul2013,P20Jul2013,P21Jul2013,P22Jul2013,P23Jul2013,P24Jul2013,P25Jul2013,P26Jul2013,P27Jul2013,P28Jul2013,P29Jul2013,P30Jul2013,P31Jul2013,P01Aug2013,P02Aug2013,P03Aug2013,P04Aug2013,P05Aug2013,P06Aug2013,P07Aug2013,P08Aug2013,P09Aug2013,P10Aug2013,P11Aug2013,P12Aug2013,P13Aug2013,P14Aug2013,P15Aug2013,P16Aug2013,P17Aug2013,P18Aug2013,P19Aug2013,P20Aug2013,P21Aug2013,P22Aug2013,P23Aug2013,P24Aug2013,P25Aug2013,P26Aug2013,P27Aug2013,P28Aug2013,P29Aug2013,P30Aug2013,P31Aug2013,P01Sep2013,P02Sep2013,P03Sep2013,P04Sep2013,P05Sep2013,P06Sep2013,P07Sep2013,P08Sep2013,P09Sep2013,P10Sep2013,P11Sep2013,P12Sep2013,P13Sep2013,P14Sep2013,P15Sep2013,P16Sep2013,P17Sep2013,P18Sep2013,P19Sep2013,P20Sep2013,P21Sep2013,P22Sep2013,P23Sep2013,P24Sep2013,P25Sep2013,P26Sep2013,P27Sep2013,P28Sep2013,P29Sep2013,P30Sep2013,P01Oct2013,P02Oct2013,P03Oct2013,P04Oct2013,P05Oct2013,P06Oct2013,P07Oct2013,P08Oct2013,P09Oct2013,P10Oct2013,P11Oct2013,P12Oct2013,P13Oct2013,P14Oct2013,P15Oct2013,P16Oct2013,P17Oct2013,P18Oct2013,P19Oct2013,P20Oct2013,P21Oct2013,P22Oct2013,P23Oct2013,P24Oct2013,P25Oct2013,P26Oct2013,P27Oct2013,P28Oct2013,P29Oct2013,P30Oct2013,P31Oct2013,P01Nov2013,P02Nov2013,P03Nov2013,P04Nov2013,P05Nov2013,P06Nov2013,P07Nov2013,P08Nov2013,P09Nov2013,P10Nov2013,P11Nov2013,P12Nov2013,P13Nov2013,P14Nov2013,P15Nov2013,P16Nov2013,P17Nov2013,P18Nov2013,P19Nov2013,P20Nov2013,P21Nov2013,P22Nov2013,P23Nov2013,P24Nov2013,P25Nov2013,P26Nov2013,P27Nov2013,P28Nov2013,P29Nov2013,P30Nov2013,P01Dec2013,P02Dec2013,P03Dec2013,P04Dec2013,P05Dec2013,P06Dec2013,P07Dec2013,P08Dec2013,P09Dec2013,P10Dec2013,P11Dec2013,P12Dec2013,P13Dec2013,P14Dec2013,P15Dec2013,P16Dec2013,P17Dec2013,P18Dec2013,P19Dec2013,P20Dec2013,P21Dec2013,P22Dec2013,P23Dec2013,P24Dec2013,P25Dec2013,P26Dec2013,P27Dec2013,P28Dec2013,P29Dec2013,P30Dec2013,P31Dec2013,P01Jan2014,P02Jan2014,P03Jan2014,P04Jan2014,P05Jan2014,P06Jan2014,P07Jan2014,P08Jan2014,P09Jan2014,P10Jan2014,P11Jan2014,P12Jan2014,P13Jan2014,P14Jan2014,P15Jan2014,P16Jan2014,P17Jan2014,P18Jan2014,P19Jan2014,P20Jan2014,P21Jan2014,P22Jan2014,P23Jan2014,P24Jan2014,P25Jan2014,P26Jan2014,P27Jan2014,P28Jan2014,P29Jan2014,P30Jan2014,P31Jan2014,P01Feb2014,P02Feb2014,P03Feb2014,P04Feb2014,P05Feb2014,P06Feb2014,P07Feb2014,P08Feb2014,P09Feb2014,P10Feb2014,P11Feb2014,P12Feb2014,P13Feb2014,P14Feb2014,P15Feb2014,P16Feb2014,P17Feb2014,P18Feb2014,P19Feb2014,P20Feb2014,P21Feb2014,P22Feb2014,P23Feb2014,P24Feb2014,P25Feb2014,P26Feb2014,P27Feb2014,P28Feb2014,P01Mar2014,P02Mar2014,P03Mar2014,P04Mar2014,P05Mar2014,P06Mar2014,P07Mar2014,P08Mar2014,P09Mar2014,P10Mar2014,P11Mar2014,P12Mar2014,P13Mar2014,P14Mar2014,P15Mar2014,P16Mar2014,P17Mar2014,P18Mar2014,P19Mar2014,P20Mar2014,P21Mar2014,P22Mar2014,P23Mar2014,P24Mar2014,P25Mar2014,P26Mar2014,P27Mar2014,P28Mar2014,P29Mar2014,P30Mar2014,P31Mar2014,P01Apr2014,P02Apr2014,P03Apr2014,P04Apr2014,P05Apr2014,P06Apr2014,P07Apr2014,P08Apr2014,P09Apr2014,P10Apr2014,P11Apr2014,P12Apr2014,P13Apr2014,P14Apr2014,P15Apr2014,P16Apr2014,P17Apr2014,P18Apr2014,P19Apr2014,P20Apr2014,P21Apr2014,P22Apr2014,P23Apr2014,P24Apr2014,P25Apr2014,P26Apr2014,P27Apr2014,P28Apr2014,P29Apr2014,P30Apr2014,P01May2014,P02May2014,P03May2014,P04May2014,P05May2014,P06May2014,P07May2014,P08May2014,P09May2014,P10May2014,P11May2014,P12May2014,P13May2014,P14May2014,P15May2014,P16May2014,P17May2014,P18May2014,P19May2014,P20May2014,P21May2014,P22May2014,P23May2014,P24May2014,P25May2014,P26May2014,P27May2014,P28May2014,P29May2014,P30May2014,P31May2014,P01Jun2014,P02Jun2014,P03Jun2014,P04Jun2014,P05Jun2014,P06Jun2014,P07Jun2014,P08Jun2014,P09Jun2014,P10Jun2014,P11Jun2014,P12Jun2014,P13Jun2014,P14Jun2014,P15Jun2014,P16Jun2014,P17Jun2014,P18Jun2014,P19Jun2014,P20Jun2014,P21Jun2014,P22Jun2014,P23Jun2014,P24Jun2014,P25Jun2014,P26Jun2014,P27Jun2014,P28Jun2014,P29Jun2014,P30Jun2014,P01Jul2014,P02Jul2014,P03Jul2014,P04Jul2014,P05Jul2014,P06Jul2014,P07Jul2014,P08Jul2014,P09Jul2014,P10Jul2014,P11Jul2014,P12Jul2014,P13Jul2014,P14Jul2014,P15Jul2014,P16Jul2014,P17Jul2014,P18Jul2014,P19Jul2014,P20Jul2014,P21Jul2014,P22Jul2014,P23Jul2014,P24Jul2014,P25Jul2014,P26Jul2014,P27Jul2014,P28Jul2014,P29Jul2014,P30Jul2014,P31Jul2014,P01Aug2014,P02Aug2014,P03Aug2014,P04Aug2014,P05Aug2014,P06Aug2014,P07Aug2014,P08Aug2014,P09Aug2014,P10Aug2014,P11Aug2014,P12Aug2014,P13Aug2014,P14Aug2014,P15Aug2014,P16Aug2014,P17Aug2014,P18Aug2014,P19Aug2014,P20Aug2014,P21Aug2014,P22Aug2014,P23Aug2014,P24Aug2014,P25Aug2014,P26Aug2014,P27Aug2014,P28Aug2014,P29Aug2014,P30Aug2014,P31Aug2014,P01Sep2014,P02Sep2014,P03Sep2014,P04Sep2014,P05Sep2014,P06Sep2014,P07Sep2014,P08Sep2014,P09Sep2014,P10Sep2014,P11Sep2014,P12Sep2014,P13Sep2014,P14Sep2014,P15Sep2014,P16Sep2014,P17Sep2014,P18Sep2014,P19Sep2014,P20Sep2014,P21Sep2014,P22Sep2014,P23Sep2014,P24Sep2014,P25Sep2014,P26Sep2014,P27Sep2014,P28Sep2014,P29Sep2014,P30Sep2014,P01Oct2014,P02Oct2014,P03Oct2014,P04Oct2014,P05Oct2014,P06Oct2014,P07Oct2014,P08Oct2014,P09Oct2014,P10Oct2014,P11Oct2014,P12Oct2014,P13Oct2014,P14Oct2014,P15Oct2014,P16Oct2014,P17Oct2014,P18Oct2014,P19Oct2014,P20Oct2014,P21Oct2014,P22Oct2014,P23Oct2014,P24Oct2014,P25Oct2014,P26Oct2014,P27Oct2014,P28Oct2014,P29Oct2014,P30Oct2014,P31Oct2014,P01Nov2014,P02Nov2014,P03Nov2014,P04Nov2014,P05Nov2014,P06Nov2014,P07Nov2014,P08Nov2014,P09Nov2014,P10Nov2014,P11Nov2014,P12Nov2014,P13Nov2014,P14Nov2014,P15Nov2014,P16Nov2014,P17Nov2014,P18Nov2014,P19Nov2014,P20Nov2014,P21Nov2014,P22Nov2014,P23Nov2014,P24Nov2014,P25Nov2014,P26Nov2014,P27Nov2014,P28Nov2014,P29Nov2014,P30Nov2014,P01Dec2014,P02Dec2014,P03Dec2014,P04Dec2014,P05Dec2014,P06Dec2014,P07Dec2014,P08Dec2014,P09Dec2014,P10Dec2014,P11Dec2014,P12Dec2014,P13Dec2014,P14Dec2014,P15Dec2014,P16Dec2014,P17Dec2014,P18Dec2014,P19Dec2014,P20Dec2014,P21Dec2014,P22Dec2014,P23Dec2014,P24Dec2014,P25Dec2014,P26Dec2014,P27Dec2014,P28Dec2014,P29Dec2014,P30Dec2014,P31Dec2014,P01Jan2015,P02Jan2015,P03Jan2015,P04Jan2015,P05Jan2015,P06Jan2015,P07Jan2015,P08Jan2015,P09Jan2015,P10Jan2015,P11Jan2015,P12Jan2015,P13Jan2015,P14Jan2015,P15Jan2015,P16Jan2015,P17Jan2015,P18Jan2015,P19Jan2015,P20Jan2015,P21Jan2015,P22Jan2015,P23Jan2015,P24Jan2015,P25Jan2015,P26Jan2015,P27Jan2015,P28Jan2015,P29Jan2015,P30Jan2015,P31Jan2015,P01Feb2015,P02Feb2015,P03Feb2015,P04Feb2015,P05Feb2015,P06Feb2015,P07Feb2015,P08Feb2015,P09Feb2015,P10Feb2015,P11Feb2015,P12Feb2015,P13Feb2015,P14Feb2015,P15Feb2015,P16Feb2015,P17Feb2015,P18Feb2015,P19Feb2015,P20Feb2015,P21Feb2015,P22Feb2015,P23Feb2015,P24Feb2015,P25Feb2015,P26Feb2015,P27Feb2015,P28Feb2015,P01Mar2015,P02Mar2015,P03Mar2015,P04Mar2015,P05Mar2015,P06Mar2015,P07Mar2015,P08Mar2015,P09Mar2015,P10Mar2015,P11Mar2015,P12Mar2015,P13Mar2015,P14Mar2015,P15Mar2015,P16Mar2015,P17Mar2015,P18Mar2015,P19Mar2015,P20Mar2015,P21Mar2015,P22Mar2015,P23Mar2015,P24Mar2015,P25Mar2015,P26Mar2015,P27Mar2015,P28Mar2015,P29Mar2015,P30Mar2015,P31Mar2015,P01Apr2015,PMaxValue type: index possible_keys: ESN key: ESN key_len: 21 ref: NULL rows: 1193 Extra: Using where; Using index; Using join buffer (Block Nested Loop) *************************** 3. row *************************** id: 1 select_type: SIMPLE table: tblUnits partitions: NULL type: ref possible_keys: ESN key: ESN key_len: 16 ref: test.du.ESN rows: 1 Extra: Using index condition; Using where *************************** 4. row *************************** id: 1 select_type: SIMPLE table: tblModelPrefixes partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using where; Using join buffer (Block Nested Loop) 4 rows in set (0.04 sec) If I reduce the above query to the case which will return based off of the given condition I get this: mysql [localhost] {msandbox} (test) > EXPLAIN partitions (SELECT count(*) as Count FROM test.tblDataSentSMS du JOIN test.vwUnits un on (du.ESN = un.ESN and un.CarrierName = 'Cingular') WHERE du.DateSent Between DATE_SUB(date_format(now(), '%Y-%m-9'), interval 1 MONTH) AND DATE_SUB(date_format(now(), '%Y-%m-%d'), interval 1 day))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tblCellularCarriers partitions: NULL type: ref possible_keys: PRIMARY key: PRIMARY key_len: 50 ref: const rows: 1 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: du partitions: P21Nov2012,P10May2014,P11May2014,P12May2014,P13May2014,P14May2014,P15May2014,P16May2014,P17May2014,P18May2014,P19May2014,P20May2014,P21May2014,P22May2014,P23May2014,P24May2014,P25May2014,P26May2014,P27May2014,P28May2014,P29May2014,P30May2014,P31May2014,P01Jun2014,P02Jun2014,P03Jun2014,P04Jun2014,P05Jun2014,P06Jun2014,P07Jun2014,P08Jun2014,P09Jun2014,P10Jun2014,P11Jun2014,P12Jun2014,P13Jun2014,P14Jun2014,P15Jun2014,P16Jun2014,P17Jun2014,P18Jun2014,P19Jun2014 type: index possible_keys: ESN key: ESN key_len: 21 ref: NULL rows: 372 Extra: Using where; Using index; Using join buffer (Block Nested Loop) *************************** 3. row *************************** id: 1 select_type: SIMPLE table: tblUnits partitions: NULL type: ref possible_keys: ESN key: ESN key_len: 16 ref: test.du.ESN rows: 1 Extra: Using index condition; Using where *************************** 4. row *************************** id: 1 select_type: SIMPLE table: tblModelPrefixes partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using where; Using join buffer (Block Nested Loop) 4 rows in set (0.10 sec) You can see that ONLY the partitions that matter are scanned. But I would expect the first query to do the same thing.