Hi, we have an update query that runs daily on tables that are recreated and reloaded via load data infile each day. Usually the query completes within 4 mins but occasionally it is still running after several hours, and from looking at the explain plan this is due to it sometimes not using the primary keys. What would be reason be for it sometimes not using them? They are still identified as possible keys. Below are the explain plans for each scenario:
NORMAL RUN:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 64314
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: UPDATE
table: schedules
partitions: NULL
type: ref
possible_keys: PRIMARY,contributionschedules_pk
key: PRIMARY
key_len: 52
ref: calculatedvalues.contractnumber,calculatedvalues.contributor
rows: 1
filtered: 100.00
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: cs
partitions: NULL
type: range
possible_keys: contributions_a_contractnumber
key: contributions_a_contractnumber
key_len: 47
ref: NULL
rows: 19296410
filtered: 0.33
Extra: Using where; Using temporary; Using filesort
*************************** 4. row ***************************
id: 2
select_type: DERIVED
table: sch
partitions: NULL
type: ref
possible_keys: PRIMARY,contributionschedules_pk
key: PRIMARY
key_len: 52
ref: customer_staging.cs.contractnumber,customer_staging.cs.contributor
rows: 1
filtered: 100.00
Extra: NULL
------------------------------------------------------------------------
LONG RUN:
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: schedules
partitions: NULL
type: ALL
possible_keys: PRIMARY,contributionschedules_pk
key: NULL
key_len: NULL
ref: NULL
rows: 1458933
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 53
ref: customer_staging.schedules.contractnumber,customer_staging.schedules.contributor
rows: 7306
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: cs
partitions: NULL
type: range
possible_keys: contributions_a_contractnumber
key: contributions_a_contractnumber
key_len: 47
ref: NULL
rows: 21920198
filtered: 0.33
Extra: Using where; Using temporary; Using filesort
*************************** 4. row ***************************
id: 2
select_type: DERIVED
table: sch
partitions: NULL
type: ALL
possible_keys: PRIMARY,contributionschedules_pk
key: NULL
key_len: NULL
ref: NULL
rows: 1458933
filtered: 10.00
Extra: Using where; Using join buffer (Block Nested Loop)