MySQL Forums
Forum List  »  Optimizer & Parser

Query no longer uses index, takes much longer to run
Posted by: Richard Cunningham
Date: January 09, 2012 12:36PM

We have an application that runs the following query under MySQL 5.1:

select e.campaign, pant.source_id , pation.participation_status_id, pation.id
from event e, event_participation pation, participant pant, user u
where e.Salesforce_id = pation.event_id
and pant.salesforce_id=pation.participant_id
and pation.is_campaign_synced=0
and e.campaign is not null
and pant.source_id like '003%'
and u.user_salesforce_ID = e.Owner_ID
and u.user_org_ID = 'constant'

Until recently, it ran quickly, using an index on the "pant" table:

+----+-------------+--------+------+-----------------------------------------------------+------------------------+---------+----------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------------------------------------------+------------------------+---------+----------------------------------+------+-------------+
| 1 | SIMPLE | u | ref | user_org_id | user_org_id | 35 | const | 1 | Using where |
| 1 | SIMPLE | e | ref | salesforceIdunique,event_owner_id | event_owner_id | 35 | ActevaRSVP.u.user_salesforce_ID | 4 | Using where |
| 1 | SIMPLE | pation | ref | participation_participant_id,participation_event_id | participation_event_id | 35 | ActevaRSVP.e.Salesforce_ID | 220 | Using where |
| 1 | SIMPLE | pant | ref | uni_participant | uni_participant | 35 | ActevaRSVP.pation.Participant_ID | 1 | Using where |
+----+-------------+--------+------+-----------------------------------------------------+------------------------+---------+----------------------------------+------+-------------+


It recently stopped using the uni_participant index and now takes hours instead of seconds to run. Here is how the explain of the query looks now:

+----+-------------+--------+------+-----------------------------------------------------+------------------------------+---------+-------------------------------+--------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------------------------------------------+------------------------------+---------+-------------------------------+--------+--------------------------------+
| 1 | SIMPLE | u | ref | user_org_id | user_org_id | 35 | const | 397 | Using where |
| 1 | SIMPLE | pant | ALL | uni_participant | NULL | NULL | NULL | 922477 | Using where; Using join buffer |
| 1 | SIMPLE | pation | ref | participation_participant_id,participation_event_id | participation_participant_id | 35 | ActevaRSVP.pant.Salesforce_Id | 1 | Using where |
| 1 | SIMPLE | e | ref | salesforceIdunique,event_owner_id | salesforceIdunique | 35 | ActevaRSVP.pation.Event_ID | 1 | Using where |
+----+-------------+--------+------+-----------------------------------------------------+------------------------------+---------+-------------------------------+--------+--------------------------------+

We have not changed the code or database schema in over a year, and this started happening last week. It's difficult to have the code changed, so we're looking for a way to fix this outside of the code, like indexing or MySQL configuration. We have set max_seeks_for_key=1000, which restored it using uni_participant at first, but it stopped using it again after a day. We also tried mysqldump'ing the pant table and recreating it, but no help there.

We would appreciate any ideas from anyone up to this challenge. Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Query no longer uses index, takes much longer to run
1805
January 09, 2012 12:36PM


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.