Quote
Are the ref,rows and Extra cols missing for the last row of the second explain'd query?
Sorry, an copy and paste failure ;-) 'ref' is! empty
here the full:
*** 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 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 80605 Range checked for
each record (index
map: 0x1)
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' ))) )
Ps.:
Quote
You could try a multi-col index on (p_catalogversion, TypePkString) or the other way round I suppose. I wouldn't expect it to help much though.
I've created the index but it doesn't affect the execution time ;-(
Edited 3 time(s). Last edit at 01/24/2007 09:30AM by Toxic Head.