MySQL Forums
Forum List  »  Optimizer & Parser

MySQL 5.1.31 -- Same query scans widely different # of rows
Posted by: Blake Harps
Date: April 07, 2011 02:54PM

We are experiencing an issue where a handful of similar queries will occasionally spike in the # of rows examined. Typically the queries will examine between 100k & 500k rows; however, they will at times start scanning between 2.2M & 16.5M rows. What would cause the optimizer to be taking wildly different routes for the same query?

Here is the explain for one of the queries in question:
mysql> explain extended /* select csdpo from CustomerSdpo as csdpo join csdpo.listings as listing join listing.listingDisplayStatuses as lds join csdpo.acceptingLocationGroup as alg join alg.acceptingLocations as acceptingLocation where 1=1 and lds.effectiveBeginDate <= :now and lds.effectiveEndDate > :now and lds.displayStatusReason = :displayStatusReason and listing.lineOfBusiness = :lineOfBusiness and ((acceptingLocation.stateCode = :state) or (acceptingLocation.stateCode = '' and acceptingLocation.countryCode = :country) 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_, customersd0_.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_ASSIGNED_ID as SYSTEM11_59_ from CUST_SDPO customersd0_ inner join LISTING listings1_ on customersd0_.CUST_SDPO_ID=listings1_.CUST_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 acceptingl3_.ALG_ID=acceptingl4_.ALG_ID where 1=1 and listingdis2_.EFFECTIVE_BEGIN_DATE<='2011-03-30 14:46:23' and listingdis2_.EFFECTIVE_END_DATE>'2011-03-30 14:46:23' and listingdis2_.DISPLAY_STAT_REAS_ID=2 and listings1_.LINE_OF_BUSINESS_ID=11 and (acceptingl4_.STATE_PROVINCE_CODE='MT' or acceptingl4_.STATE_PROVINCE_CODE='' and acceptingl4_.COUNTRY_CODE='USA' or acceptingl3_.ACCEPTS_ALL_LOC_FLG=1);
+----+-------------+--------------+--------+------------------------------------------------+-------------+---------+---------------------------------------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+--------+------------------------------------------------+-------------+---------+---------------------------------------+------+----------+--------------------------+
| 1 | SIMPLE | listings1_ | ref | PRIMARY,LIST_AK_UID,LIST_CSDPO_FK,LIST_LOB_FK | LIST_LOB_FK | 8 | const | 2834 | 100.00 | |
| 1 | SIMPLE | customersd0_ | eq_ref | PRIMARY,CSDPO_ALG_FK | PRIMARY | 8 | enterprise_db.listings1_.CUST_SDPO_ID | 1 | 100.00 | |
| 1 | SIMPLE | acceptingl3_ | eq_ref | PRIMARY,ALG_ID | PRIMARY | 8 | enterprise_db.customersd0_.ALG_ID | 1 | 100.00 | |
| 1 | SIMPLE | listingdis2_ | ref | LDS_LIST_FK,LDS_DSR_FK,LDS_BED_IDX,LDS_EED_IDX | LDS_LIST_FK | 8 | enterprise_db.listings1_.LISTING_ID | 9 | 100.00 | Using where |
| 1 | SIMPLE | acceptingl4_ | ref | AL_AK_UID,SPC_IDX,CC_IDX,AL_ALG_FK | AL_AK_UID | 8 | enterprise_db.acceptingl3_.ALG_ID | 109 | 100.00 | Using where; Using index |
+----+-------------+--------------+--------+------------------------------------------------+-------------+---------+---------------------------------------+------+----------+--------------------------+
5 rows in set, 1 warning (0.00 sec)

Indexes for the tables that are being touched:

SHOW INDEX FROM LISTING;
+---------+------------+---------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+---------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| LISTING | 0 | PRIMARY | 1 | LISTING_ID | A | 29452 | NULL | NULL | | BTREE | |
| LISTING | 0 | LIST_SA_UID | 1 | SYSTEM_ASSIGNED_ID | A | 29452 | NULL | NULL | | BTREE | |
| LISTING | 0 | LIST_AK_UID | 1 | CUST_SDPO_ID | A | 29452 | NULL | NULL | | BTREE | |
| LISTING | 0 | LIST_AK_UID | 2 | LINE_OF_BUSINESS_ID | A | 29452 | NULL | NULL | | BTREE | |
| LISTING | 1 | LIST_CSDPO_FK | 1 | CUST_SDPO_ID | A | 29452 | NULL | NULL | | BTREE | |
| LISTING | 1 | LIST_LOB_FK | 1 | LINE_OF_BUSINESS_ID | A | 12 | NULL | NULL | | BTREE | |
+---------+------------+---------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.01 sec)

SHOW INDEX FROM CUST_SDPO;
+-----------+------------+--------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
| CUST_SDPO | 0 | PRIMARY | 1 | CUST_SDPO_ID | A | 25576 | NULL | NULL | | BTREE | |
| CUST_SDPO | 0 | CSDPO_SA_UID | 1 | SYSTEM_ASSIGNED_ID | A | 25576 | NULL | NULL | | BTREE | |
| CUST_SDPO | 0 | CSDPO_AK_UID | 1 | CUST_ID | A | 608 | NULL | NULL | | BTREE | |
| CUST_SDPO | 0 | CSDPO_AK_UID | 2 | SCHOOL_DEGREE_PROGRAM_ID | A | 25576 | NULL | NULL | | BTREE | |
| CUST_SDPO | 0 | CSDPO_AK_UID | 3 | INSTRUCTION_LOCATION_ID | A | 25576 | NULL | NULL | | BTREE | |
| CUST_SDPO | 1 | CSDPO_CA_FK | 1 | CUST_ID | A | 448 | NULL | NULL | | BTREE | |
| CUST_SDPO | 1 | CSDPO_ALG_FK | 1 | ALG_ID | A | 5115 | NULL | NULL | | BTREE | |
| CUST_SDPO | 1 | CSDPO_DRD_FK | 1 | DRAFT_RFI_DOC_ID | A | 1 | NULL | NULL | YES | BTREE | |
| CUST_SDPO | 1 | CSDPO_PRD_FK | 1 | PUB_RFI_DOC_ID | A | 1504 | NULL | NULL | YES | BTREE | |
| CUST_SDPO | 1 | CSDPO_IL_FK | 1 | INSTRUCTION_LOCATION_ID | A | 3653 | NULL | NULL | | BTREE | |
| CUST_SDPO | 1 | CSDPO_SDP_FK | 1 | SCHOOL_DEGREE_PROGRAM_ID | A | 25576 | NULL | NULL | | BTREE | |
+-----------+------------+--------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
11 rows in set (0.00 sec)


SHOW INDEX FROM LISTING_DISPLAY_STAT;
+----------------------+------------+-------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+-------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| LISTING_DISPLAY_STAT | 0 | PRIMARY | 1 | LDS_ID | A | 413649 | NULL | NULL | | BTREE | |
| LISTING_DISPLAY_STAT | 1 | LDS_LIST_FK | 1 | LISTING_ID | A | 59092 | NULL | NULL | | BTREE | |
| LISTING_DISPLAY_STAT | 1 | LDS_DSR_FK | 1 | DISPLAY_STAT_REAS_ID | A | 14 | NULL | NULL | | BTREE | |
| LISTING_DISPLAY_STAT | 1 | LDS_BED_IDX | 1 | EFFECTIVE_BEGIN_DATE | A | 103412 | NULL | NULL | | BTREE | |
| LISTING_DISPLAY_STAT | 1 | LDS_EED_IDX | 1 | EFFECTIVE_END_DATE | A | 137883 | NULL | NULL | | BTREE | |
+----------------------+------------+-------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

SHOW INDEX FROM ACCEPTING_LOCATION;
+--------------------+------------+-----------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+-----------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| ACCEPTING_LOCATION | 0 | PRIMARY | 1 | AL_ID | A | 444984 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 0 | AL_AK_UID | 1 | ALG_ID | A | 4362 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 0 | AL_AK_UID | 2 | POSTAL_CODE | A | 444984 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 0 | AL_AK_UID | 3 | STATE_PROVINCE_CODE | A | 444984 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 0 | AL_AK_UID | 4 | COUNTRY_CODE | A | 444984 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 1 | PC_IDX | 1 | POSTAL_CODE | A | 88996 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 1 | SPC_IDX | 1 | STATE_PROVINCE_CODE | A | 149 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 1 | CC_IDX | 1 | COUNTRY_CODE | A | 13 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 1 | AL_ALG_FK | 1 | ALG_ID | A | 4278 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 1 | LOC_IDX | 1 | POSTAL_CODE | A | 63569 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 1 | LOC_IDX | 2 | STATE_PROVINCE_CODE | A | 63569 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION | 1 | LOC_IDX | 3 | COUNTRY_CODE | A | 63569 | NULL | NULL | | BTREE | |
+--------------------+------------+-----------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
12 rows in set (0.01 sec)

SHOW INDEX FROM ACCEPTING_LOCATION_GROUP;
+--------------------------+------------+----------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------------+------------+----------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| ACCEPTING_LOCATION_GROUP | 0 | PRIMARY | 1 | ALG_ID | A | 1369 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION_GROUP | 0 | HASH_KEY | 1 | MD5_OF_LOCATIONS | A | 1369 | NULL | NULL | | BTREE | |
| ACCEPTING_LOCATION_GROUP | 1 | ALG_ID | 1 | ALG_ID | A | 1369 | NULL | NULL | | BTREE | |
+--------------------------+------------+----------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

These particular queries happen every 3 seconds or so and are generated by our web application. Under normal conditions, the queries return in less than a second. However, once the # of rows examined spikes, the queries start building up and taking an extremely long amount of time. Typically in excess or 150 seconds. If we kill the long running queries, we will return to a normal state and the queries start scanning fewer rows again.

Entries from the Slow Query log:

# User@Host: webui[webui] @ localhost [127.0.0.1]
# Query_time: 764.779203 Lock_time: 0.000177 Rows_sent: 1304 Rows_examined: 5394443
SET timestamp=1300212282;
/* 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);

# Query_time: 15.745860 Lock_time: 0.000207 Rows_sent: 1304 Rows_examined: 349488
SET timestamp=1300214916;
/* 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 11:45:54' and listingdis2_.EFFE
CTIVE_END_DATE>'2011-03-15 11:45:54' 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);

The first is the issue. The second is taking a long time because there are 100s of the first stacking up before being killed by a script that kills any non-sleeping, non-system process over 125 seconds.

Any guidance would be appreciated,

Blake H

Options: ReplyQuote




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.