Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -1 down vote favorite

I have a query like this:

SELECT 
    BE.NAME as NAME,
    BE.PREVIEW_TEXT as PREVIEW_TEXT,
    BE.ID as ID, 
    FPV1.VALUE as PROPERTY_COUNTRY_VALUE, 
    FPV1.ID as PROPERTY_COUNTRY_VALUE_ID, 
    FPV2.VALUE as PROPERTY_TOUR_TYPE_VALUE, 
    FPV2.ID as PROPERTY_TOUR_TYPE_VALUE_ID, 
    FPV3.VALUE as PROPERTY_DAYS_VALUE, 
    FPV3.ID as PROPERTY_DAYS_VALUE_ID, 
    FPV4.VALUE as PROPERTY_PRICE_VALUE, 
    FPV4.ID as PROPERTY_PRICE_VALUE_ID, 
    FPV5.VALUE as PROPERTY_CURRENCY_VALUE, 
    FPV5.ID as PROPERTY_CURRENCY_VALUE_ID, 
    FPV6.VALUE as PROPERTY_DATE_FROM_VALUE, 
    FPV6.ID as PROPERTY_DATE_FROM_VALUE_ID, 
    FPV7.VALUE as PROPERTY_DATE_TO_VALUE, 
    FPV7.ID as PROPERTY_DATE_TO_VALUE_ID, 
    FPV8.VALUE as PROPERTY_FLIGHT_FROM_VALUE, 
    FPV8.ID as PROPERTY_FLIGHT_FROM_VALUE_ID, 
    FPV9.VALUE as PROPERTY_MT_ID_VALUE, 
    FPV9.ID as PROPERTY_MT_ID_VALUE_ID, 
    FPV10.VALUE as PROPERTY_PRICE_FILE_VALUE, 
    FPV10.ID as PROPERTY_PRICE_FILE_VALUE_ID,
    BE.PREVIEW_PICTURE as PREVIEW_PICTURE, 
    FPV11.VALUE as PROPERTY_PRICE_HAND_VALUE, 
    FPV11.ID as PROPERTY_PRICE_HAND_VALUE_ID, 
    FPV0.VALUE as PROPERTY_MAN_PRICE_VALUE, 
    FPV0.ID as PROPERTY_MAN_PRICE_VALUE_ID, 
    FPV12.VALUE as PROPERTY_ROOM_PRICE_VALUE, 
    FPV12.ID as PROPERTY_ROOM_PRICE_VALUE_ID, 
    FPEN0.VALUE as PROPERTY_SPEC_OFFER_VALUE, 
    FPEN0.ID as PROPERTY_SPEC_OFFER_ENUM_ID, 
    FPV13.ID as PROPERTY_SPEC_OFFER_VALUE_ID, 
    FPV14.VALUE as PROPERTY_ENGL_NAME_VALUE, 
    FPV14.ID as PROPERTY_ENGL_NAME_VALUE_ID, 
    FPEN1.VALUE as PROPERTY_EARLY_BOOKING_VALUE, 
    FPEN1.ID as PROPERTY_EARLY_BOOKING_ENUM_ID, 
    FPV15.ID as PROPERTY_EARLY_BOOKING_VALUE_ID, 
    FPEN2.VALUE as PROPERTY_SUPER_JET_VALUE, 
    FPEN2.ID as PROPERTY_SUPER_JET_ENUM_ID, 
    FPV16.ID as PROPERTY_SUPER_JET_VALUE_ID, 
    FPEN3.VALUE as PROPERTY_BEST_PRICE_VALUE, 
    FPEN3.ID as PROPERTY_BEST_PRICE_ENUM_ID, 
    FPV17.ID as PROPERTY_BEST_PRICE_VALUE_ID, 
    FPEN4.VALUE as PROPERTY_EVENT_VALUE, 
    FPEN4.ID as PROPERTY_EVENT_ENUM_ID, 
    FPV18.ID as PROPERTY_EVENT_VALUE_ID,
    BE.SORT as SORT,
    BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE         
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 
    LEFT JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID 
        AND FP0.CODE='MAN_PRICE' 
    INNER JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID 
        AND FP1.CODE='COUNTRY' 
    INNER JOIN b_iblock_property FP2 ON FP2.IBLOCK_ID = B.ID 
        AND FP2.CODE='TOUR_TYPE' 
    LEFT JOIN b_iblock_property FP3 ON FP3.IBLOCK_ID = B.ID 
        AND FP3.CODE='DAYS' 
    LEFT JOIN b_iblock_property FP4 ON FP4.IBLOCK_ID = B.ID 
        AND FP4.CODE='PRICE' 
    LEFT JOIN b_iblock_property FP5 ON FP5.IBLOCK_ID = B.ID 
        AND FP5.CODE='CURRENCY' 
    LEFT JOIN b_iblock_property FP6 ON FP6.IBLOCK_ID = B.ID 
        AND FP6.CODE='DATE_FROM' 
    LEFT JOIN b_iblock_property FP7 ON FP7.IBLOCK_ID = B.ID 
        AND FP7.CODE='DATE_TO' 
    INNER JOIN b_iblock_property FP8 ON FP8.IBLOCK_ID = B.ID 
        AND FP8.CODE='FLIGHT_FROM' 
    LEFT JOIN b_iblock_property FP9 ON FP9.IBLOCK_ID = B.ID 
        AND FP9.CODE='MT_ID' 
    LEFT JOIN b_iblock_property FP10 ON FP10.IBLOCK_ID = B.ID 
        AND FP10.CODE='PRICE_FILE' 
    LEFT JOIN b_iblock_property FP11 ON FP11.IBLOCK_ID = B.ID 
        AND FP11.CODE='PRICE_HAND' 
    LEFT JOIN b_iblock_property FP12 ON FP12.IBLOCK_ID = B.ID 
        AND FP12.CODE='ROOM_PRICE' 
    LEFT JOIN b_iblock_property FP13 ON FP13.IBLOCK_ID = B.ID 
        AND FP13.CODE='SPEC_OFFER' 
    LEFT JOIN b_iblock_property FP14 ON FP14.IBLOCK_ID = B.ID 
        AND FP14.CODE='ENGL_NAME' 
    LEFT JOIN b_iblock_property FP15 ON FP15.IBLOCK_ID = B.ID 
        AND FP15.CODE='EARLY_BOOKING' 
    LEFT JOIN b_iblock_property FP16 ON FP16.IBLOCK_ID = B.ID 
        AND FP16.CODE='SUPER_JET' 
    LEFT JOIN b_iblock_property FP17 ON FP17.IBLOCK_ID = B.ID 
        AND FP17.CODE='BEST_PRICE' 
    LEFT JOIN b_iblock_property FP18 ON FP18.IBLOCK_ID = B.ID 
        AND FP18.CODE='EVENT' 
    LEFT 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 
    LEFT JOIN b_iblock_element_property FPV3 
        ON FPV3.IBLOCK_PROPERTY_ID = FP3.ID 
        AND FPV3.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV4 
        ON FPV4.IBLOCK_PROPERTY_ID = FP4.ID 
        AND FPV4.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV5
        ON FPV5.IBLOCK_PROPERTY_ID = FP5.ID 
        AND FPV5.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV6
        ON FPV6.IBLOCK_PROPERTY_ID = FP6.ID 
        AND FPV6.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV7 
        ON FPV7.IBLOCK_PROPERTY_ID = FP7.ID 
        AND FPV7.IBLOCK_ELEMENT_ID = BE.ID 
    INNER JOIN b_iblock_element_property FPV8 
        ON FPV8.IBLOCK_PROPERTY_ID = FP8.ID 
        AND FPV8.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV9 
        ON FPV9.IBLOCK_PROPERTY_ID = FP9.ID 
        AND FPV9.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV10 
        ON FPV10.IBLOCK_PROPERTY_ID = FP10.ID 
        AND FPV10.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV11 
        ON FPV11.IBLOCK_PROPERTY_ID = FP11.ID 
        AND FPV11.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV12 
        ON FPV12.IBLOCK_PROPERTY_ID = FP12.ID 
        AND FPV12.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV13 
        ON FPV13.IBLOCK_PROPERTY_ID = FP13.ID 
        AND FPV13.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV14 
        ON FPV14.IBLOCK_PROPERTY_ID = FP14.ID 
        AND FPV14.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV15 
        ON FPV15.IBLOCK_PROPERTY_ID = FP15.ID 
        AND FPV15.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV16 
        ON FPV16.IBLOCK_PROPERTY_ID = FP16.ID 
        AND FPV16.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV17 
        ON FPV17.IBLOCK_PROPERTY_ID = FP17.ID 
        AND FPV17.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_element_property FPV18 
        ON FPV18.IBLOCK_PROPERTY_ID = FP18.ID 
        AND FPV18.IBLOCK_ELEMENT_ID = BE.ID 
    LEFT JOIN b_iblock_property_enum FPEN0 
        ON FPEN0.PROPERTY_ID = FPV13.IBLOCK_PROPERTY_ID 
        AND FPV13.VALUE_ENUM = FPEN0.ID 
    LEFT JOIN b_iblock_property_enum FPEN1 
        ON FPEN1.PROPERTY_ID = FPV15.IBLOCK_PROPERTY_ID 
        AND FPV15.VALUE_ENUM = FPEN1.ID 
    LEFT JOIN b_iblock_property_enum FPEN2 
        ON FPEN2.PROPERTY_ID = FPV16.IBLOCK_PROPERTY_ID 
        AND FPV16.VALUE_ENUM = FPEN2.ID 
    LEFT JOIN b_iblock_property_enum FPEN3 
        ON FPEN3.PROPERTY_ID = FPV17.IBLOCK_PROPERTY_ID 
        AND FPV17.VALUE_ENUM = FPEN3.ID 
    LEFT JOIN b_iblock_property_enum FPEN4 
        ON FPEN4.PROPERTY_ID = FPV18.IBLOCK_PROPERTY_ID 
        AND FPV18.VALUE_ENUM = FPEN4.ID 
    INNER JOIN 
        (
            SELECT DISTINCT BSE.IBLOCK_ELEMENT_ID 
            FROM b_iblock_section_element BSE 
            INNER JOIN b_iblock_section BS 
                ON BSE.IBLOCK_SECTION_ID = BS.ID 
            WHERE ((BS.ID IN (137)))
        ) BES ON BES.IBLOCK_ELEMENT_ID = BE.ID 

    WHERE 1=1 AND ( ((((BE.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.IBLOCK_ID = '6')))) 
        AND ( ( (((FPV13.VALUE_ENUM IS NULL))) ) 
        AND ( (((FPV15.VALUE_ENUM IS NULL))) ) 
        AND ( (((FPV17.VALUE_ENUM IS NULL))) ) 
        AND ( (((FPV16.VALUE_ENUM IS NULL))) ) ) 
        AND ((((FPV1.VALUE_NUM = '4656')))) 
        AND ((((FPV2.VALUE_NUM = '36997')))) 
        AND (((FPV8.VALUE_NUM IN ('7191', '7190')))) ) 
        AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL))) 
    ORDER BY BE.SORT desc ,FPV0.VALUE_NUM asc

It looks scary, but it should work in a couple of seconds maximum.

Problem is it is running forever. Well, more than 900 seconds!

Here is a result of the extended explain for it:

1   PRIMARY B   const   PRIMARY PRIMARY 4   const   1   Using temporary; Using filesort
1   PRIMARY L   const   PRIMARY PRIMARY 2   const   1   Using index
1   PRIMARY FP0 ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP2 ref PRIMARY,ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   Using where
1   PRIMARY FP3 index_merge ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2,ix_iblock_property_1   53,4    NULL    1   Using intersect(ix_iblock_property_2,ix_iblock_property_1); Using where; Using index
1   PRIMARY FP4 index_merge ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2,ix_iblock_property_1   53,4    NULL    1   Using intersect(ix_iblock_property_2,ix_iblock_property_1); Using where; Using index
1   PRIMARY FP5 ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP6 index_merge ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2,ix_iblock_property_1   53,4    NULL    1   Using intersect(ix_iblock_property_2,ix_iblock_property_1); Using where; Using index
1   PRIMARY FP7 index_merge ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2,ix_iblock_property_1   53,4    NULL    1   Using intersect(ix_iblock_property_2,ix_iblock_property_1); Using where; Using index
1   PRIMARY FP8 index_merge PRIMARY,ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2,ix_iblock_property_1   53,4    NULL    1   Using intersect(ix_iblock_property_2,ix_iblock_property_1); Using where; Using index
1   PRIMARY FP9 ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_1    4   bitrix.FP2.IBLOCK_ID    2   
1   PRIMARY FP10    ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP11    ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP12    ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP13    ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP14    ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP15    ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP16    ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP17    ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY FP18    ref ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_2    53  const   1   
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    49  
1   PRIMARY BE  eq_ref  PRIMARY,ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_3,date_active_from_x,ix_iblock_element_code   PRIMARY 4   BES.IBLOCK_ELEMENT_ID   1   Using where
1   PRIMARY FPV2    ref ix_iblock_element_property_1,ix_iblock_element_property_2,VALUE_NUM,ix_iblock_element_prop_num,ix_iblock_element_property_3 ix_iblock_element_property_1    8   BES.IBLOCK_ELEMENT_ID,bitrix.FP2.ID 1   Using where
1   PRIMARY FPV8    ref ix_iblock_element_property_1,ix_iblock_element_property_2,VALUE_NUM,ix_iblock_element_prop_num,ix_iblock_element_property_3 ix_iblock_element_property_1    8   BES.IBLOCK_ELEMENT_ID,bitrix.FP8.ID 1   Using where
1   PRIMARY FPV0    ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   BES.IBLOCK_ELEMENT_ID,bitrix.FP0.ID 1   
1   PRIMARY FPV3    ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV8.IBLOCK_ELEMENT_ID,bitrix.FP3.ID 1   
1   PRIMARY FPV4    ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV2.IBLOCK_ELEMENT_ID,bitrix.FP4.ID 1   
1   PRIMARY FPV5    ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.BE.ID,bitrix.FP5.ID  1   
1   PRIMARY FPV6    ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV8.IBLOCK_ELEMENT_ID,bitrix.FP6.ID 1   
1   PRIMARY FPV7    ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV8.IBLOCK_ELEMENT_ID,bitrix.FP7.ID 1   
1   PRIMARY FPV9    ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV8.IBLOCK_ELEMENT_ID,bitrix.FP9.ID 1   
1   PRIMARY FPV10   ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV8.IBLOCK_ELEMENT_ID,bitrix.FP10.ID    1   
1   PRIMARY FPV11   ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV2.IBLOCK_ELEMENT_ID,bitrix.FP11.ID    1   
1   PRIMARY FPV12   ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV2.IBLOCK_ELEMENT_ID,bitrix.FP12.ID    1   
1   PRIMARY FPV13   ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV2.IBLOCK_ELEMENT_ID,bitrix.FP13.ID    1   Using where
1   PRIMARY FPV14   ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV2.IBLOCK_ELEMENT_ID,bitrix.FP14.ID    1   
1   PRIMARY FPV15   ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV2.IBLOCK_ELEMENT_ID,bitrix.FP15.ID    1   Using where
1   PRIMARY FPV16   ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV2.IBLOCK_ELEMENT_ID,bitrix.FP16.ID    1   Using where
1   PRIMARY FPV17   ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV2.IBLOCK_ELEMENT_ID,bitrix.FP17.ID    1   Using where
1   PRIMARY FPV18   ref ix_iblock_element_property_1,ix_iblock_element_property_2,ix_iblock_element_property_3  ix_iblock_element_property_1    8   bitrix.FPV2.IBLOCK_ELEMENT_ID,bitrix.FP18.ID    1   
1   PRIMARY FP1 index_merge PRIMARY,ix_iblock_property_1,ix_iblock_property_2   ix_iblock_property_1,ix_iblock_property_2   4,53    NULL    2   Using intersect(ix_iblock_property_1,ix_iblock_property_2); Using where; Using index
1   PRIMARY FPV1    ref ix_iblock_element_property_1,ix_iblock_element_property_2,VALUE_NUM,ix_iblock_element_prop_num,ix_iblock_element_property_3 ix_iblock_element_property_1    8   BES.IBLOCK_ELEMENT_ID,bitrix.FP1.ID 1   Using where
1   PRIMARY FPEN0   eq_ref  PRIMARY,ux_iblock_property_enum PRIMARY 4   bitrix.FPV13.VALUE_ENUM 1   
1   PRIMARY FPEN1   eq_ref  PRIMARY,ux_iblock_property_enum PRIMARY 4   bitrix.FPV15.VALUE_ENUM 1   
1   PRIMARY FPEN2   eq_ref  PRIMARY,ux_iblock_property_enum PRIMARY 4   bitrix.FPV16.VALUE_ENUM 1   
1   PRIMARY FPEN3   eq_ref  PRIMARY,ux_iblock_property_enum PRIMARY 4   bitrix.FPV17.VALUE_ENUM 1   
1   PRIMARY FPEN4   eq_ref  PRIMARY,ux_iblock_property_enum PRIMARY 4   bitrix.FPV18.VALUE_ENUM 1   
2   DERIVED BS  const   PRIMARY PRIMARY 4       1   Using index
2   DERIVED BSE ref ux_iblock_section_element   ux_iblock_section_element   4       49  Using where; Using index

Why should it take so long to process such a query?

Here are indexes for tables:

b_iblock (35 rows)

  PRIMARY KEY  (`ID`),
  KEY `ix_iblock` (`IBLOCK_TYPE_ID`,`LID`,`ACTIVE`)

b_iblock_element (~6 millions rows)

  PRIMARY KEY  (`ID`),
  KEY `ix_iblock_element_1` (`IBLOCK_ID`,`IBLOCK_SECTION_ID`),
  KEY `ix_iblock_element_4` (`IBLOCK_ID`,`XML_ID`,`WF_PARENT_ELEMENT_ID`),
  KEY `ix_iblock_element_3` (`WF_PARENT_ELEMENT_ID`),
  KEY `date_active_from_x` (`ACTIVE_FROM`,`ID`),
  KEY `ix_iblock_element_code` (`IBLOCK_ID`,`CODE`),
  KEY `i_iblock_element_code` (`CODE`)

b_iblock_property (166 rows)

  PRIMARY KEY  (`ID`),
  KEY `ix_iblock_property_1` (`IBLOCK_ID`),
  KEY `ix_iblock_property_2` (`CODE`)

b_iblock_element_property

  PRIMARY KEY  (`ID`),
  KEY `ix_iblock_element_property_1` (`IBLOCK_ELEMENT_ID`,`IBLOCK_PROPERTY_ID`),
  KEY `ix_iblock_element_property_2` (`IBLOCK_PROPERTY_ID`),
  KEY `ix_iblock_element_prop_enum` (`VALUE_ENUM`,`IBLOCK_PROPERTY_ID`),
  KEY `VALUE_NUM` (`VALUE_NUM`),
  KEY `ix_iblock_element_prop_num` (`VALUE_NUM`,`IBLOCK_PROPERTY_ID`),
  KEY `ix_iblock_element_property_3` (`IBLOCK_PROPERTY_ID`,`IBLOCK_ELEMENT_ID`)

Update:

This query is generated automatically by a CMS system. Same queries work just fine in similar situations.

This particular query returns 0 rows as result (it is correct)

Today it is needs just 98ms to run.

What I need help is to understand why it needed 900 seconds to run several days ago?

Why do other similar queries take so long to process sometimes?

I executed this query with SQL_NO_CACHE parameter to prevent using the MySQL cache.

share|improve this question
1  
Please make a readable yet relevant snippet of your code (and strip unnecessary parentheses from the WHERE clause). And how do you know that it should complete in seconds? Used to work earlier? – dezso Jul 4 '12 at 10:46
I guess it used to work when there were 6K rows. Now, with the 6M rows ... – ypercube Jul 4 '12 at 14:24
Well, to be mildly constructive: I feel that no query optimizer on earth will digest such a query with so many rows affected. I would try and rethink the query and very possibly the underlying table structure as well. This resembles to me an EAV data model, but with only less than 20 different (and fixed) attributes it is considered (at least by me) an overkill - and certainly is a performance killer. – dezso Jul 5 '12 at 9:08
@deszo: The largest table has only 6M rows. MySQL optimizers may not be that good but they are not the only ones on Earth. – ypercube Jul 5 '12 at 11:00
MyISAM or InnoDB engine? – ypercube Jul 5 '12 at 11:33
show 5 more comments

1 Answer

up vote 5 down vote

Rewriting your query first, in a form that is, I hope, readable:

SELECT

SELECT BE.NAME as NAME, BE.PREVIEW_TEXT as PREVIEW_TEXT, BE.ID as ID
     , FPV1.VALUE as PROPERTY_COUNTRY_VALUE, FPV1.ID as PROPERTY_COUNTRY_VALUE_ID
     --- various FPVxx.VALUE and FPENx.VALUE columns
     , FPV18.ID as PROPERTY_EVENT_VALUE_ID
     , BE.SORT as SORT, BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE

FROM

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 

   LEFT JOIN b_iblock_property FP0   ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='MAN_PRICE' 
  INNER JOIN b_iblock_property FP1   ON FP1.IBLOCK_ID = B.ID AND FP1.CODE='COUNTRY' 
----------- 19 joins, some INNER, some LEFT
   LEFT JOIN b_iblock_property FP18  ON FP18.IBLOCK_ID = B.ID AND FP18.CODE='EVENT' 


   LEFT 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 
------------ 19 more joins
   LEFT JOIN b_iblock_element_property FPV18  ON FPV18.IBLOCK_PROPERTY_ID = FP18.ID 
                                             AND FPV18.IBLOCK_ELEMENT_ID = BE.ID 


   LEFT JOIN b_iblock_property_enum FPEN0 
                  ON FPEN0.PROPERTY_ID = FPV13.IBLOCK_PROPERTY_ID 
                 AND FPV13.VALUE_ENUM = FPEN0.ID 
------ 5 joins
   LEFT JOIN b_iblock_property_enum FPEN4 
                  ON FPEN4.PROPERTY_ID = FPV18.IBLOCK_PROPERTY_ID 
                 AND FPV18.VALUE_ENUM = FPEN4.ID 

  INNER JOIN 
      ( SELECT DISTINCT BSE.IBLOCK_ELEMENT_ID 
        FROM b_iblock_section_element BSE 
            INNER JOIN b_iblock_section BS 
                ON BSE.IBLOCK_SECTION_ID = BS.ID 
        WHERE BS.ID IN (137) 
       ) BES 
     ON BES.IBLOCK_ELEMENT_ID = BE.ID

WHERE

WHERE 1=1 
  AND BE.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.IBLOCK_ID = '6'
  AND FPV13.VALUE_ENUM IS NULL 
  AND FPV15.VALUE_ENUM IS NULL 
  AND FPV17.VALUE_ENUM IS NULL 
  AND FPV16.VALUE_ENUM IS NULL 
  AND FPV1.VALUE_NUM = '4656' 
  AND FPV2.VALUE_NUM = '36997'
  AND FPV8.VALUE_NUM IN ('7191', '7190')
  AND BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL

ORDER BY

ORDER BY 
    BE.SORT desc 
  , FPV0.VALUE_NUM asc ;

First, If I'm counting correctly, that is 47 joins and is approaching MySQL's hard limit on 61 joins. So be careful not to allow adding (many) more conditions.

Second, this is obviously an EAV design, so it's not the easiest to optimize for performance. I think one of the main problems is some compound indices missing. You also are not telling us whether the tables are using MyISAM or InnoDB engine.

For example, you don't have an index on b_iblock_property(CODE, IBLOCK_ID, ID). I think the (19) joins of the type:

... JOIN b_iblock_property FP0   ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='MAN_PRICE'

would be improved with this index.

You may also see difference in performance by adding appropriate compound indices on the b_iblock_element_property table, like: (VALUE_NUM, IBLOCK_PROPERTY_ID, IBLOCK_ELEMENT_ID) and probably a few others. Not 100% sure which ones would help, as testing needs to be done with your tables (and the sizes you expect to have) and with the various queries that you are hitting the database. You surely don't want to add too many indices.

A third issue is the overall design. Leaving aside whether EAV should or not be used - lets assume you cannot change that - you should examine carefully whether some column combinations could be declared as UNIQUE and thus the complex join conditions you have:

   LEFT JOIN b_iblock_element_property FPV0   ON FPV0.IBLOCK_PROPERTY_ID = FP0.ID 
                                             AND FPV0.IBLOCK_ELEMENT_ID = BE.ID

could be altered, to have columns from two tables and not three.

share|improve this answer
Thanks for your analysis of the query @ypercube – nozh Jul 6 '12 at 7:17
Can it really be a big problem a missing index on a small table b_iblock_propery ? (it just has 166 rows in it) – nozh Jul 6 '12 at 7:18
I would try to make annother index on b_iblock_element_property like b_iblock_element_property table, like: (VALUE_NUM, IBLOCK_PROPERTY_ID, IBLOCK_ELEMENT_ID) – nozh Jul 6 '12 at 7:20
And one more thing! This query now runs lightning fast. 98 ms. (I run it with no cache) Without adding or removing indexes or configuring DB. – nozh Jul 6 '12 at 7:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.