|
Product: Enterprise Server
Product version: 10.6.12
Description: limit clause changes explain plan
The following query runs very fast 0.05 second:
explain SELECT
|
t.PocId AS PocId,
|
t.ID AS AAId,
|
br.emmId AS EmmId,
|
br.mmId AS mmId,
|
br.vaaId AS VaaId
|
FROM
|
huerta b
|
INNER JOIN bie br FORCE INDEX (IX_bie_comp_emmid_mmid_vaaid) on b.ID = br.BetID
|
INNER JOIN aa t on b.AAId = t.ID
|
WHERE
|
(
|
t.PocId,
|
br.emmId,
|
br.mmId,
|
br.vaaId
|
) IN (
|
(8465071, 10844962, 20515, 0),
|
(8465071, 10844956, 21261, 0),
|
(8465071, 10844959, 607300, 32312443)
|
)
|
1 SIMPLE br range IX_bie_comp_emmid_mmid_vaaid IX_bie_comp_emmid_mmid_vaaid 20 (null) 1618 Using index condition
|
1 SIMPLE b eq_ref PRIMARY,huerta_uidx1,IX_huerta_AAID PRIMARY 8 cc.br.BetID 1
|
1 SIMPLE t eq_ref PRIMARY,IX_aa_IsSxBet,IX_aa_PocID,IX_aa_PocDateCreated,IX_aa_PocID_DateCreated,IX_aa_ID_has_transaction PRIMARY 8 cc.b.AAID 1 Using where
|
Now below query runs for 15 minutes and its scanning 648975797 rows
explain SELECT
|
t.PocId AS PocId,
|
t.ID AS AAId,
|
br.emmId AS EmmId,
|
br.mmId AS mmId,
|
br.vaaId AS VaaId
|
FROM
|
huerta b
|
INNER JOIN bie br FORCE INDEX (IX_bie_comp_emmid_mmid_vaaid) on b.ID = br.BetID
|
INNER JOIN aa t on b.AAID = t.ID
|
WHERE
|
(
|
t.PocId,
|
br.emmId,
|
br.mmId,
|
br.vaaId
|
) IN (
|
(8465071, 10844962, 20515, 0),
|
(8465071, 10844956, 21261, 0),
|
(8465071, 10844959, 607300, 32312443)
|
) limit 10
|
1 SIMPLE br ALL IX_bie_comp_emmid_mmid_vaaid (null) (null) (null) 648975797 Using where
|
1 SIMPLE b eq_ref PRIMARY,huerta_uidx1,IX_huerta_AAID PRIMARY 8 cc.br.BetID 1
|
1 SIMPLE t eq_ref PRIMARY,IX_aa_IsSxBet,IX_aa_PocID,IX_aa_PocDateCreated,IX_aa_PocID_DateCreated,IX_aa_ID_has_transaction PRIMARY 8 cc.b.AAID 1 Using where
|
|