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
