Запрос:
explain SELECT
DISTINCT BE.ID as ID,
BE.IBLOCK_ID as IBLOCK_ID,
BE.CODE as CODE,
BE.XML_ID as XML_ID,
BE.NAME as NAME,
BE.ACTIVE as ACTIVE,
BE.SORT as SORT,
BE.PREVIEW_TEXT as PREVIEW_TEXT,
BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE,
DATE_FORMAT(BE.DATE_CREATE,
'%d.%m.%Y %H:%i:%s') as DATE_CREATE,
BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,
B.DETAIL_PAGE_URL as DETAIL_PAGE_URL,
BE.DETAIL_PICTURE as DETAIL_PICTURE,
BE.PREVIEW_PICTURE as PREVIEW_PICTURE,
FPS0.PROPERTY_2778 as PROPERTY_SALE_PRICE_VALUE,
concat(BE.ID ,
':' ,
2778) as PROPERTY_SALE_PRICE_VALUE_ID,
L.DIR as LANG_DIR,
BE.XML_ID as EXTERNAL_ID,
B.IBLOCK_TYPE_ID as IBLOCK_TYPE_ID,
B.CODE as IBLOCK_CODE,
B.XML_ID as IBLOCK_EXTERNAL_ID,
B.LID as LID ,
PRD.AVAILABLE as AVAILABLE,
PRD.TYPE as TYPE,
PRD.BUNDLE as BUNDLE,
PRD.QUANTITY as QUANTITY,
IF (PRD.QUANTITY_TRACE = 'D',
'Y',
PRD.QUANTITY_TRACE) as QUANTITY_TRACE,
IF (PRD.CAN_BUY_ZERO = 'D',
'N',
PRD.CAN_BUY_ZERO) as CAN_BUY_ZERO,
PRD.MEASURE as MEASURE,
IF (PRD.SUBSCRIBE = 'D',
'Y',
PRD.SUBSCRIBE) as SUBSCRIBE,
PRD.VAT_ID as VAT_ID,
PRD.VAT_INCLUDED as VAT_INCLUDED,
PRD.WEIGHT as WEIGHT,
PRD.WIDTH as WIDTH,
PRD.LENGTH as LENGTH,
PRD.HEIGHT as HEIGHT,
PRD.PRICE_TYPE as PAYMENT_TYPE,
PRD.RECUR_SCHEME_LENGTH as RECUR_SCHEME_LENGTH,
PRD.RECUR_SCHEME_TYPE as RECUR_SCHEME_TYPE,
PRD.TRIAL_PRICE_ID as TRIAL_PRICE_ID
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_element_prop_s1 FPS0
ON FPS0.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN
b_iblock_1_index FC
on FC.ELEMENT_ID = BE.ID
left join
b_catalog_product as PRD
on (
PRD.ID = BE.ID
)
WHERE
1=1
AND (
(
FC.SECTION_ID = 4892
AND FC.FACET_ID = 1
AND FC.VALUE_NUM = 0
AND FC.VALUE in (
0
)
)
AND (
(
(
(
BE.ACTIVE='Y'
)
)
)
)
AND (
(
(
(
BE.IBLOCK_ID = '1'
)
)
)
)
)
AND (
(
(
BE.WF_STATUS_ID=1
AND BE.WF_PARENT_ELEMENT_ID IS NULL
)
)
)
ORDER BY
PRD.AVAILABLE desc ,
FPS0.PROPERTY_2778 desc LIMIT 0,
36
Всю эту историю дико тормозит ORDER BY ( с ним отрабатывает за 1 сек, без него 0.05). Как только не химичил с индексами, результата нуль. Прикладываю explain
id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra
1 SIMPLE B const PRIMARY PRIMARY 4 const 1 100.00 Using temporary; Using filesort
1 SIMPLE L const PRIMARY PRIMARY 6 const 1 100.00
1 SIMPLE BE ref PRIMARY,ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_3,ix_iblock_element_code,ix_perf_b_iblock_element_1 ix_iblock_element_1 4 const 124469 2.50 Using index condition; Using where
1 SIMPLE FC eq_ref PRIMARY,IX_b_iblock_1_index_0,IX_b_iblock_1_index_1 PRIMARY 24 const,const,const,const,dev22_rukodelov_db.BE.ID 1 100.00 Using index
1 SIMPLE FPS0 eq_ref PRIMARY PRIMARY 4 dev22_rukodelov_db.BE.ID 1 100.00
1 SIMPLE PRD eq_ref PRIMARY PRIMARY 4 dev22_rukodelov_db.BE.ID 1 100.00
Так как таблица BE является камнем преткновения (124к строк), то и индексы создавать решил для неё. (прошу извинить за неудобный формат explain, по другому приложить не получается)
Индексы BE
Что можно сделать, чтобы ускорить запрос?
UPD: скриншот explain
Простой
Простой
