Почему MySQL не использует соответствующие индексы, когда они доступны? При вынуждении их использовать, разница в произв ⇐ MySql

[Anonymous]

У меня есть следующее оператор SELECT, где все столбцы должным образом индексируются с столбцом, на котором они соединяются (то есть все FKS), за исключением cte.templateid, который вообще не имеет индексов - это временный набор результатов. < /p>

Код: Выделить всё

# 'Selected columns were based on fields from each table (e.g. SELECT t.Name, c.Label, etc),'
# 'the index behaviour appears the same with `SELECT *` however; all tables JOINed are necessary'

SELECT *
FROM CTE
INNER JOIN template T on T.TemplateID = CTE.TemplateID
INNER JOIN member ME on ME.TemplateID = T.TemplateID
INNER JOIN memberfigures MF on MF.MemberID = ME.MemberID
INNER JOIN membercategories MC ON ME.MemberID = MC.MemberID
INNER JOIN categories C ON C.CategoryID = MC.CategoryID
WHERE MF.FigureTypeID = 1
AND C.CategoryStandardID = 1;
< /code>
, где CTE является либо рекурсивным общим выражением таблицы, либо результатом CTE, хранящегося во временной таблице с формой: < /p>
# 'At most 4 records'
CREATE TEMPORARY TABLE CTE (TemplateID INT);
INSERT INTO CTE (TemplateID) VALUES (1);
< /code>
Нет никакой разницы, если я делаю присоединение или подзадность. И нет разницы, добавляю ли я результаты CTE во временную таблицу, чтобы присоединиться к. Позже объяснить заявления дают почти идентичные результаты. Например, < /p>
# 'TEMPORARY TABLE'
CREATE TEMPORARY TABLE CTE (TemplateID INT);
...
INNER JOIN template T on T.TemplateID = CTE.TemplateID

# 'OR Sub-Query'
AND T.TemplateID IN (SELECT TemplateID FROM CTE);

# 'OR WITH CTE'
WITH RECURSIVE CTE (TemplateID) ...
< /code>
Рекурсивный CTE мгновенно выполняется при запуске само по себе, а внутренние операции соединения любой другой таблицы без него также почти мгновенно (пара миллисекунд). Результаты CTE будут не более 4-5 записей, которые при введении AS и T.TemplateID в (, , ... ); 

(то есть результат CTE без касания таблицы) также является мгновенным. Но на самом деле их объединение, производительность становится 100-1000x медленнее, чем запуска оба отдельно.table type possible_keys key key_len ref rows filtered Extra
CTE ALL NULL NULL NULL NULL 1 100 Using where
T eq_ref PRIMARY PRIMARY 4 CTE.TemplateID 1 100 NULL
MF ref FK_Figure_Artifact_idx,FK_Figure_Member_idx FK_Figure_Artifact_idx 2 const 500000 100 NULL
ME eq_ref PRIMARY,FK_Member_Template_idx PRIMARY 4 MF.MemberID 1 100 Using where
MC ref FK_Category_Member_idx,FK_Category_CatStandard_idx FK_Category_Member_idx 4 MF.MemberID 2 100 NULL
C eq_ref PRIMARY,FK_Category_CatStandard_idx PRIMARY 2 MC.CategoryID 1 50 Using where
< /code>
FK_FIGURE_ARTIFACT_IDX - это FK на совершенно не связанной таблице с вышеуказанным запросом. Без соединения на CTE я получаю в основном одинаковую выводом от объяснения. Затем я «принудил» следующие индексы, и запрос перешел с более чем 30 секунд до менее 0,3 секунды, а новые выбранные индексы объяснения выглядели более разумными: < /p>
/*+ INDEX(ME FK_Member_Template_idx) INDEX(MF FK_Figure_Member_idx) */

table type possible_keys key key_len ref rows filtered Extra
CTE ALL NULL NULL NULL NULL 1 100 Using where
T eq_ref PRIMARY PRIMARY 4 CTE.TemplateID 1 100 NULL
ME ref FK_Member_Template_idx FK_Member_Template_idx 5 CTE.TemplateID 300000 100 NULL
MF ref FK_Figure_Member_idx FK_Figure_Member_idx 4 ME.MemberID 60 1.33 Using where
MC ref FK_Category_Member_idx,FK_CatStandard_Category_idx FK_Category_Member_idx 4 ME.MemberID 2 100 NULL
C eq_ref PRIMARY,FK_Category_CatStandard_idx PRIMARY 2 MC.CategoryID 1 50 Using where
< /code>
У меня есть 2 основных вопроса: < /p>

Зачем делать это соединение /подкованная справочник так резко? />
Я переопределял индексы по умолчанию чрезвычайно редко в прошлом, и кажется странным, что я должен сделать это здесь. < /li>
< /ul>
< /li>
< /ul>

Подробнее здесь: https://stackoverflow.com/questions/795 ... to-use-the