I've got the following SELECT statement where all columns are properly indexed with the column they're joining on. I.e. all PKs & FKs have a corresponding, single-column index shown later ending in _idx. The columns FigureTypeID & CategoryStandardID have the indexes FK_Figure_Artifact_idx and FK_Category_CatStandard_idx respectively. The single exception is CTE.TemplateID, which has no indexes at all - it's a temporary result set.

# '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;

Where CTE is either a recursive common table expression, or the result of a CTE stored in a temporary table with the form:

# 'At most 4 records'
CREATE TEMPORARY TABLE CTE (TemplateID INT);
INSERT INTO CTE (TemplateID) VALUES (1);

There's no difference whether I do a JOIN or a subquery. And no difference whether I add the results of the CTE to a TEMPORARY TABLE to JOIN onto instead. Later EXPLAIN statements produce nearly identical results. E.g.

# '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) ...

The RECURSIVE CTE executes instantaneously when run by itself, and the INNER JOIN operations of every other table without it is also nearly instantaneous (a couple milliseconds). The results from the CTE will be at most 4-5 records, which if entered as AND T.TemplateID IN (<Record 1>, <Record 2>, ... <Record 5>); (i.e. the result of the CTE without touching the table) is also instantaneous. But actually putting them together, the performance becomes 100-1000x slower than running both separately.

Running an EXPLAIN statement on the TEMPORARY TABLE approach:

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

FK_Figure_Artifact_idx is an FK on a totally unrelated table to the above query seemingly used for WHERE MF.FigureTypeID = 1. Without that WHERE clause, it selects FK_Figure_Member_idx which is the index associated with the JOIN.

Without the JOIN on CTE I get largely the same output from EXPLAIN. I then "forced" the following indexes, and the query went from over 30 seconds down to under 0.3 seconds, with the EXPLAIN statement's new chosen indexes looking more sensible to me:

/*+ 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

I have 2 main questions:

• Why doing this JOIN / Sub-Query tanked the performance so dramatically?
  • What was the optimiser doing to end up with this?
• Is the approach I took sensible?
  • I've overridden default indexes extremely rarely in the past, and it seems strange I'd have to do it here.