Re: MySQL 5.1.31 -- Same query scans widely different # of rows

[Rick James]

> the times of heavy insertion do not correlate with our spikes in rows_examined
I would not expect them to correlate.

select  csdpo
    from  CustomerSdpo as csdpo
    join  csdpo.listings as listing
    join  listing.listingDisplayStatuses as lds
    join  csd po.acceptingLocationGroup as alg
    join  alg.acceptingLocations as acceptingLocation
    where  1=1
      and  lds.effectiveBeginDate <= :no w
      and  lds.effectiveEndDate > :now
      and  lds.displayStatusReason = :displayStatusReason
      and  listing.lineOfBusiness = :lineOfBusi ness
      and  ((acceptingLocation.stateCode = :state)
              or  (acceptingLocation.stateCode = ''
                      and  acceptingLocation.countryCode = :c ountry)
              or  alg.acceptAllLocations=true) */ 
select  customersd0_.CUST_SDPO_ID as CUST1_59_,
        customersd0_.VERSION as VERSION59 _,
        customersd0_.ALG_ID as ALG5_59_, customersd0_.ALT_CAMPUS_CODE as ALT3_59_,
        customersd0_.ALT_PROGRAM_CODE as ALT4_59_,
        cust omersd0_.CUST_ID as CUST6_59_,
        customersd0_.DRAFT_RFI_DOC_ID as DRAFT7_59_,
        customersd0_.INSTRUCTION_LOCATION_ID as INSTRUCT8 _59_,
        customersd0_.PUB_RFI_DOC_ID as PUB9_59_,
        customersd0_.SCHOOL_DEGREE_PROGRAM_ID as SCHOOL10_59_,
        customersd0_.SYSTEM_ASS IGNED_ID as SYSTEM11_59_
    from  CUST_SDPO customersd0_
    inner join  LISTING listings1_ on customersd0_.CUST_SDPO_ID=listings1_.CU ST_SDPO_ID
    inner join  LISTING_DISPLAY_STAT listingdis2_ on listings1_.LISTING_ID=listingdis2_.LISTING_ID
    inner join  ACCEPTING _LOCATION_GROUP acceptingl3_ on customersd0_.ALG_ID=acceptingl3_.ALG_ID
    inner join  ACCEPTING_LOCATION acceptingl4_ on accepti ngl3_.ALG_ID=acceptingl4_.ALG_ID
    where  1=1
      and  listingdis2_.EFFECTIVE_BEGIN_DATE<='2011-03-15 10:49:31'
      and  listingdis2_.EFFE CTIVE_END_DATE>'2011-03-15 10:49:31'
      and  listingdis2_.DISPLAY_STAT_REAS_ID=2
      and  listings1_.LINE_OF_BUSINESS_ID=11
      and  (accep tingl4_.STATE_PROVINCE_CODE='MT'
              or  acceptingl4_.STATE_PROVINCE_CODE=''
              and  acceptingl4_.COUNTRY_CODE='USA'
              or  acceptingl3_.A CCEPTS_ALL_LOC_FLG=1);

An inside-out range like
and listingdis2_.EFFECTIVE_BEGIN_DATE<='2011-03-15 10:49:31'
and listingdis2_.EFFE CTIVE_END_DATE>'2011-03-15 10:49:31'
is not optimizable.

A compound index with these two fields might be best for the second SELECT:
and listingdis2_.DISPLAY_STAT_REAS_ID=2
and listings1_.LINE_OF_BUSINESS_ID=11

OR makes optimization difficult:
and (accep tingl4_.STATE_PROVINCE_CODE='MT'
or acceptingl4_.STATE_PROVINCE_CODE=''
and acceptingl4_.COUNTRY_CODE='USA'
or acceptingl3_.A CCEPTS_ALL_LOC_FLG=1);