0
270
VistasMySQL IN clause vs. OR clauses affect performance - Why is OR faster than IN?
I have been tasked with rewriting a slow query. I solved my performance problem, however I'm restless because I don't understand why one of the approaches I tried is faster than the other.
Query 1 (takes ~13 seconds on the website, ~0.2 seconds in PHPMYADMIN) :
SELECT t.USER_ID, COUNT(DISTINCT r1.id1) as count_matches
FROM b_squad_member_result as t
INNER JOIN (SELECT m.IBLOCK_ELEMENT_ID as id1, (m.IBLOCK_ELEMENT_ID + 2) as id2, (m.IBLOCK_ELEMENT_ID + 4) as id3
FROM b_iblock_element_prop_s3 as m
WHERE m.PROPERTY_8 IS NULL) as r1
ON t.MATCH_ID IN(id1, id2, id3)
INNER JOIN b_uts_user as u ON u.VALUE_ID = t.USER_ID
AND u.UF_ID_TEAM = 2228
GROUP BY t.USER_ID
Execution plan for Query 1
Query 2 (takes ~0.2 seconds on the website, ~0.2 seconds in PHPMYADMIN) :
SELECT t.USER_ID, COUNT(DISTINCT r1.id1) as count_matches
FROM b_squad_member_result as t
INNER JOIN (SELECT m.IBLOCK_ELEMENT_ID as id1, (m.IBLOCK_ELEMENT_ID + 2) as id2, (m.IBLOCK_ELEMENT_ID + 4) as id3
FROM b_iblock_element_prop_s3 as m
WHERE m.PROPERTY_8 IS NULL) as r1
ON t.MATCH_ID = id1 OR t.MATCH_ID = id2 OR t.MATCH_ID = id3
INNER JOIN b_uts_user as u ON u.VALUE_ID = t.USER_ID
AND u.UF_ID_TEAM = 2228
GROUP BY t.USER_ID
Execution plan for Query 2 :
I first went with Query 1 as in PHPMYADMIN it was meeting my performance expectations. However, on the website itself, the query took much more time. After trying lots of different solutions I just decided to change the IN clause for t.MATCH_ID = id1 OR t.MATCH_ID = id2 OR t.MATCH_ID = id3 and this works as fast as it is supposed to. However I would like to understand why is the second approach faster. I've read that the IN clause is transformed into multiple OR clauses before the actual execution. Can it really affect performance that much?
1 Respuestas
Responde la pregunta0
The parentheses are different. Your first query puts the parens around r1 with the ON inside; it would be outside, as you did with the second query.
I see that the EXPLAINs are different; I don't know if this is because of the parens or IN vs OR.
Testing a value against multiple columns is really bad for performance. It is usually fixable by a schema change. I call the anti-pattern "spraying an array across columns". It is usually better to have another table with [up to] 3 rows for those ids. If the ids are strings, the FULLTEXT may be a better approach.
While the previous paragraph is valid is general, it does not apply in you case, since idn is computed from a single column IBLOCK_ELEMENT_ID. WTF?
I really need to see SHOW CREATE TABLE to fully help you.
These indexes, if you don't already have them, may help:
u: (UF_ID_TEAM, VALUE_ID)
m: (PROPERTY_8, IBLOCK_ELEMENT_ID)
COUNT(DISTINCT r1.id1) -- the DISTINCT (and its overhead) could probably be better done by adding a GROUP BY to the derived table for r1. Well, that may not be useful -- it depends on whether there are multiple rows of u involved. But then we get into whether you will stumble over ONLY_FULL_GROUP_BY. So, please explain whether the tables are 1:many or 1:1.
(I agree that this is an XY question.)