MySQL Forums
Forum List  »  Optimizer & Parser

Query not always using indexes
Posted by: K DBA
Date: January 06, 2017 05:48AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Query not always using indexes
1533
January 06, 2017 05:48AM
521
January 06, 2017 12:28PM
568
January 09, 2017 05:18AM


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.