Query no longer uses index, takes much longer to run
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.