Bug #48761 Query with multiple join with the same table is slow with index and fast without
Submitted: 13 Nov 2009 14:16 Modified: 27 Dec 2009 23:39
Reporter: Igor Usoltsev Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.0.51a-3ubuntu5.4-log OS:Linux
Assigned to: CPU Architecture:Any
Tags: INDEX, join

[13 Nov 2009 14:16] Igor Usoltsev
Description:
Problem with slow query from bitrix LAMP application:
SELECT COUNT(DISTINCT BE.ID) as C FROM b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
INNER JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='NEW_ATTR_METALL'
INNER JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID AND FP1.CODE='NEW_ATTR_VSTAVKA'
INNER JOIN b_iblock_property FP2 ON FP2.IBLOCK_ID = B.ID AND FP2.CODE='NEW_NO_DOUBLE'
INNER JOIN b_iblock_element_property FPV0 ON FPV0.IBLOCK_PROPERTY_ID = FP0.ID AND FPV0.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV1 ON FPV1.IBLOCK_PROPERTY_ID = FP1.ID AND FPV1.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV2 ON FPV2.IBLOCK_PROPERTY_ID = FP2.ID AND FPV2.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN ( SELECT DISTINCT B.ID FROM b_iblock B INNER JOIN b_iblock_site BS ON BS.IBLOCK_ID = B.ID
WHERE ((((BS.SITE_ID='s1')))) AND ((((B.ID = '4')))) AND ((((B.ACTIVE='Y')))) ) BB ON BB.ID = BE.IBLOCK_ID
LEFT
JOIN b_catalog_price CAT_P9 ON (CAT_P9.PRODUCT_ID = BE.ID AND CAT_P9.CATALOG_GROUP_ID = 9)
AND ((CAT_P9.QUANTITY_FROM <= 1 OR CAT_P9.QUANTITY_FROM IS NULL)
AND (CAT_P9.QUANTITY_TO >= 1 OR CAT_P9.QUANTITY_TO IS NULL)) LEFT JOIN b_catalog_product CAT_PR ON (CAT_PR.ID = BE.ID)
LEFT JOIN b_catalog_iblock CAT_IB ON ((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0) AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)
LEFT JOIN b_catalog_vat CAT_VAT ON (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID))
WHERE 1=1 AND ( ((((FPV0.VALUE_ENUM = '264'))))
AND ((((FPV1.VALUE_ENUM = '384'))))
AND (((NOT(CAT_P9.PRICE IS NULL))))
AND ((((BE.IBLOCK_ID = '4'))))
AND ((((B.ACTIVE='Y'))))
AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL)
AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL)))
AND ((((BE.ACTIVE='Y'))))
AND ( 1=1 )
AND ((((FPV2.VALUE_NUM > '0')))) )
AND (((BE.WF_STATUS_ID=1
AND BE.WF_PARENT_ELEMENT_ID IS NULL)))

Query is executed about 70 seconds!

show indexes from b_iblock_property;						
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type
b_iblock_property	0	PRIMARY	1	ID	A	45	 	 	 	BTREE
b_iblock_property	1	ix_iblock_property_1	1	IBLOCK_ID	A	7	 	 	 	BTREE
b_iblock_property	1	ix_iblock_property_2	1	CODE	A	22	 	 	YES	BTREE

If I exclude of index ix_iblock_property_2 usage from query by:
- dropping the index
- or adding hint IGNORE INDEX
...
INNER JOIN b_iblock_property FP0 IGNORE INDEX (ix_iblock_property_2) ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='NEW_ATTR_METALL'
INNER JOIN b_iblock_property FP1 IGNORE INDEX (ix_iblock_property_2) ON FP1.IBLOCK_ID = B.ID AND FP1.CODE='NEW_ATTR_VSTAVKA'
INNER JOIN b_iblock_property FP2 IGNORE INDEX (ix_iblock_property_2) ON FP2.IBLOCK_ID = B.ID AND FP2.CODE='NEW_NO_DOUBLE'
...
- or changing the query text
...
INNER JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='NEW_ATTR_METALL'
INNER JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID AND FP1.CODE='NEW_ATTR_VSTAVKA'
INNER JOIN b_iblock_property FP2 ON FP2.IBLOCK_ID = B.ID AND FP2.CODE='NEW_NO_DOUBLE'
...
query executes by 1 second!
Table b_iblock_property is 45 rows only

How to repeat:
Explain plan for slow query
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	 	 	 	 	1	 
1	PRIMARY	B	const	PRIMARY	PRIMARY	4	const	1	 
1	PRIMARY	L	const	PRIMARY	PRIMARY	2	const	1	Using index
1	PRIMARY	FP0	ref	PRIMARY,ix_iblock_property_1,ix_iblock_property_2	ix_iblock_property_2	53	const	1	Using where
1	PRIMARY	FP1	ref	PRIMARY,ix_iblock_property_1,ix_iblock_property_2	ix_iblock_property_2	53	const	1	Using where
1	PRIMARY	FP2	ref	PRIMARY,ix_iblock_property_1,ix_iblock_property_2	ix_iblock_property_2	53	const	1	Using where
1	PRIMARY	FPV1	ref	ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_prop_enum	ix_iblock_element_prop_enum	9	const,zoloto.FP1.ID	1	Using where
1	PRIMARY	CAT_P9	ref	IXS_CAT_PRICE_PID,IXS_CAT_PRICE_GID	IXS_CAT_PRICE_PID	8	zoloto.FPV1.IBLOCK_ELEMENT_ID,const	1	Using where
1	PRIMARY	BE	eq_ref	PRIMARY,ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_3	PRIMARY	4	zoloto.FPV1.IBLOCK_ELEMENT_ID	1	Using where
1	PRIMARY	CAT_PR	eq_ref	PRIMARY	PRIMARY	4	zoloto.FPV1.IBLOCK_ELEMENT_ID	1	 
1	PRIMARY	CAT_IB	eq_ref	PRIMARY	PRIMARY	4	const	1	 
1	PRIMARY	CAT_VAT	eq_ref	PRIMARY	PRIMARY	4	func	1	Using index
1	PRIMARY	FPV0	ref	ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_prop_enum	ix_iblock_element_prop_enum	9	const,zoloto.FP0.ID	1	Using where
1	PRIMARY	FPV2	ref	ix_iblock_element_property_1,ix_iblock_element_property_2	ix_iblock_element_property_1	8	zoloto.CAT_P9.PRODUCT_ID,zoloto.FP2.ID	2	Using where
2	DERIVED	B	const	PRIMARY	PRIMARY	4	 	1	 
2	DERIVED	BS	const	PRIMARY	PRIMARY	6	 	1	Using index

Plan for fast query:
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	 	 	 	 	1	 
1	PRIMARY	B	const	PRIMARY	PRIMARY	4	const	1	 
1	PRIMARY	L	const	PRIMARY	PRIMARY	2	const	1	Using index
1	PRIMARY	FP0	ref	PRIMARY,ix_iblock_property_1	ix_iblock_property_1	4	const	14	Using where
1	PRIMARY	FPV0	ref	ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_prop_enum	ix_iblock_element_prop_enum	9	const,zoloto.FP0.ID	1	Using where
1	PRIMARY	CAT_P9	ref	IXS_CAT_PRICE_PID,IXS_CAT_PRICE_GID	IXS_CAT_PRICE_PID	8	zoloto.FPV0.IBLOCK_ELEMENT_ID,const	1	Using where
1	PRIMARY	FPV1	ref	ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_prop_enum	ix_iblock_element_property_1	4	zoloto.FPV0.IBLOCK_ELEMENT_ID	15	Using where
1	PRIMARY	BE	eq_ref	PRIMARY,ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_3	PRIMARY	4	zoloto.CAT_P9.PRODUCT_ID	1	Using where
1	PRIMARY	CAT_PR	eq_ref	PRIMARY	PRIMARY	4	zoloto.FPV0.IBLOCK_ELEMENT_ID	1	 
1	PRIMARY	CAT_IB	eq_ref	PRIMARY	PRIMARY	4	const	1	 
1	PRIMARY	CAT_VAT	eq_ref	PRIMARY	PRIMARY	4	func	1	Using index
1	PRIMARY	FP1	eq_ref	PRIMARY,ix_iblock_property_1	PRIMARY	4	zoloto.FPV1.IBLOCK_PROPERTY_ID	1	Using where
1	PRIMARY	FPV2	ref	ix_iblock_element_property_1,ix_iblock_element_property_2	ix_iblock_element_property_1	4	zoloto.CAT_P9.PRODUCT_ID	15	Using where
1	PRIMARY	FP2	eq_ref	PRIMARY,ix_iblock_property_1	PRIMARY	4	zoloto.FPV2.IBLOCK_PROPERTY_ID	1	Using where
2	DERIVED	B	const	PRIMARY	PRIMARY	4	 	1	 
2	DERIVED	BS	const	PRIMARY	PRIMARY	6	 	1	Using index

Results remains without changes if I remake the problem index ix_iblock_property_2 to be more suitable for JOIN:

drop index ix_iblock_property_2 on b_iblock_property;
create index ix_iblock_property_2 on b_iblock_property(IBLOCK_ID, code);
analyze table b_iblock_property;
show indexes from b_iblock_property;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
b_iblock_property	0	PRIMARY	1	ID	A	45	 	 	 	BTREE	 
b_iblock_property	1	ix_iblock_property_1	1	IBLOCK_ID	A	7	 	 	 	BTREE	 
b_iblock_property	1	ix_iblock_property_12	1	IBLOCK_ID	A	7	 	 	 	BTREE	 
b_iblock_property	1	ix_iblock_property_12	2	CODE	A	45	 	 	YES	BTREE	 

Suggested fix:
How to exclude such strange server behavior?

Thanks in advance,
Igor Usoltsev
[13 Nov 2009 15:58] Valeriy Kravchuk
Please, try to repeat with a newer version of server, 5.0.86 (or wait for 5.0.88 to be released really soon) and inform about the results. Even if your (old) version had a bug, it may be already fixed.
[13 Nov 2009 16:24] Igor Usoltsev
thanks Valery
Will check this
Wouldn't you point me similar problems in MySQL bug database?
Sincerely, Igor
[27 Nov 2009 23:39] Miguel Solorzano
Please re-open if you get the same result with latest release. Thanks in advance.
[28 Dec 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".