Query Optimization - <Derived 2> Type: ALL, Key = NULL

Question

Similar questions have been asked, and I've reworked this query with every suggestion, but can't figure out the issue. When submit the following query to EXPLAIN EXTENDED I receive both 'Type' = 'ref' and a key was found for each table (although I don't know why 'isActive' is the key chosen for the derived table 'CS.'

SELECT 
    distinct
    CS.BLNUMBER
    ,CS.CONTAINER
    ,s.status
    #,s.location
    #,s.dateandtime 
FROM 
    CUSTOMERSHIPMENTS CS
INNER JOIN SHIPMENTS S 
    ON CS.BLNUMBER = S.BLNUMBER 
    AND CS.CONTAINER = S.CONTAINERNUMBER
WHERE
    cs.blnumber <>''
    and CS.CDATE > NOW() - INTERVAL 3 MONTH 
    and CS.ISACTIVE = 1
    and cs.hostcompany_id = cs.company_id
    AND S.STATUS <> ''

When I assign this join an alias and join again the 'customershipments' table, this time called 'CS2' as in:

FROM
(
SELECT 
    distinct
    CS.BLNUMBER
    ,CS.CONTAINER
    ,s.status
    #,s.location
    #,s.dateandtime 
FROM 
    CUSTOMERSHIPMENTS CS
INNER JOIN SHIPMENTS S 
    ON CS.BLNUMBER = S.BLNUMBER 
    AND CS.CONTAINER = S.CONTAINERNUMBER
WHERE
    cs.blnumber <>''
    and CS.CDATE > NOW() - INTERVAL 3 MONTH 
    and CS.ISACTIVE = 1
    and cs.hostcompany_id = cs.company_id
    AND S.STATUS <> ''
) temp

INNER JOIN CUSTOMERSHIPMENTS CS2 
ON temp.blnumber = CS2.BLNUMBER
and temp.container = cs2.container

EXPLAIN EXTENDED now immediately tells me:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY derived2    ALL NULL    NULL    NULL    NULL    45120   100.00  Using temporary

BUT "Derived2" is pointing back to EXPLAIN EXTENDED ID's 2 which is my join between CS and S. How did it go from having a key, to not finding one? I canNot figure this out.

Answer 1

A subquery has no indexes. (See footnote.) Because of that, it is advisable to plan on the subquery running first, then JOINing to the other table(s). In your case, cs2 needs the composite INDEX(BLNUMBER, container) (in either order).

Here's how the whole query will be executed:

1. Perform the subquery, storing the results into a temporary table (Engine MEMORY or MyISAM).
2. JOIN to CS2 using whatever it can of BLNUMBER and/or container.

Meanwhile, the optimal indexes for the subquery are

• CS: (ISACTIVE, CDATE) -- more on this below
• S: (BLNUMBER, CONTAINERNUMBER)

Please provide SHOW CREATE TABLE and all of the EXPLAIN. Let me guess some more...

You say that INDEX(isactive) was mysteriously picked? That would be a useful index if less than something like 20% of the table had CS.ISACTIVE = 1. Is that the case? Regardless, the composite index I suggest will be better.

Are there about 45K rows in the subquery? That takes time to generate and use.

Footnote: In 5.6, the optimizer discovers what the best index should be for each subquery, and adds it to the tmp table. However this may be irrelevant if CS2 has the index I mentioned above. That JOIN could go either way, so why bother creating an index on temp that is no better than the one on CS2.