Okay,
Here are the two 'EXPLAIN' results:
*** EXPLAIN with the whole statement ***
Query takes 50 sek+
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY item_t0 range PRIMARY, PRIMARY 92 157 Using where
Products_TypePKIndex,
Products_visibilityIDX
2 DEPENDENT SUBQUERY item_t2 ref Cat2ProdRel_LinkTarget, Cat2ProdRel_LinkTarget 92 catalog.item_t0.PK 2 Using where
Cat2ProdRel_TypePKIndex,
Cat2ProdRel_LinkSource
2 DEPENDENT SUBQUERY item_t1 ref PRIMARY, Categories_versionIDX 93 const 19970 Using where
Categories_TypePKIndex,
Categories_versionIDX,
*** EXPLAIN with the removed line 'AND (( item_t1.p_catalogversion IN ( {short list of version keys} )))' ***
Query takes only 30 ms
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY item_t0 range PRIMARY, PRIMARY 92 157 Using where
Products_TypePKIndex,
Products_visibilityIDX
2 DEPENDENT SUBQUERY item_t2 ref Cat2ProdRel_LinkTarget, Cat2ProdRel_LinkTarget 92 catalog.item_t0.PK 2 Using where
Cat2ProdRel_TypePKIndex,
Cat2ProdRel_LinkSource
2 DEPENDENT SUBQUERY item_t1 range PRIMARY Categories_TypePKIndex 92
and here's the whole statement again for a better overview:
explain SELECT item_t0.PK
FROM Products item_t0
WHERE ( item_t0.PK IN ( {A big list of Product Keys} ))
AND (item_t0.TypePkString IN ( {a shorter List if Type Keys} )
AND (( item_t0.p_approvalstatus = { one state Key } )
AND (EXISTS
(SELECT item_t1.PK
FROM Categories item_t1 JOIN Cat2ProdRel item_t2
ON item_t1.PK = item_t2.SourcePK
WHERE ( item_t2.TargetPK = item_t0.PK )
AND ((item_t1.TypePkString IN ( { two type Keys } )
AND (( item_t1.p_catalogversion IN ( { short list of version keys } )))
AND item_t2.TypePkString = { one Type Key } ))))
AND (( item_t0.p_onlinedate IS NULL OR item_t0.p_onlinedate <= '2007-01-09 00:00:00.0' )
AND ( item_t0.p_offlinedate IS NULL OR item_t0.p_offlinedate >= '2007-01-09 00:00:00.0' ))) )