CODEWITHSUNDEEP
mysql
syl.fabre
syl.fabre

Reputation: 746

Why MySQL doesn't use the index

I'm running these two different queries and I don't understand why on the first one sale table index is not used

EXPLAIN SELECT COUNT(`sale`.`saleId`) AS `nb`
FROM `sale` 
WHERE `sale`.`saleTransactionId` IN (
SELECT `transaction`.`transactionId`
FROM `transaction`  
WHERE `transaction`.`transactionId` = 87587
)

Result is

result 1 http://snag.gy/SusqF.jpg

Second one

EXPLAIN SELECT COUNT(`sale`.`saleId`) AS `nb`
FROM `sale` 
WHERE `sale`.`saleTransactionId` IN (87587)

Result is

result 2 http://snag.gy/ZgOXQ.jpg

Juste to be sure

SELECT `transaction`.`transactionId`
FROM `transaction`  
WHERE `transaction`.`transactionId` = 87587

returns juste one row

saleTransactionId is INT(11) as transactionId Both tables uses MyISAM

And sale index structure

sale index structure http://snag.gy/we3HL.jpg

Thanks!

Upvotes: 1

Views: 46

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191819

Because the subquery is a dependent subquery (due to how MySQL works). This means that the result of the inner query depends on the outer query. Thus, an index cannot be used for the outer query since the inner query relies on its results. Sounds counter intuitive, but that's what's happening.

As for a solution: don't use a subquery. Use a JOIN.

SELECT COUNT(saleId) AS nb
FROM sale
JOIN transaction ON (saleTransactionId = transactionId)
WHERE transactionId = 87587

Upvotes: 1

Related Questions