MySQL InnoDB Subquery – Slow CREATE Table from SELECT INNER JOIN

innodbMySQLsubquery

This query takes ~90 seconds:

CREATE TABLE filtered
(
    SELECT DISTINCT
    f2.cityCode, 
    f2.productCode, 
    f2.productDesc, 
    f2.inventoryDate, 
    f2.inventoryLevel
    FROM 
    etm
    INNER JOIN f2avail AS f2 ON 
    etm.cityCode = f2.cityCode AND 
    etm.tvlDate = f2.inventoryDate
);

but the subquery on its own takes ~0.07 seconds:

-- CREATE TABLE filtered
-- (
    SELECT DISTINCT
    f2.cityCode, 
    f2.productCode, 
    f2.productDesc, 
    f2.inventoryDate, 
    f2.inventoryLevel
    FROM 
    etm
    INNER JOIN f2avail AS f2 ON 
    etm.cityCode = f2.cityCode AND 
    etm.tvlDate = f2.inventoryDate
-- );

Why is that?

Following this solution, I ran OPTIMIZE TABLE f2avail (the one with ~246,000 rows) and the create query ran about 25% faster, but still slow.

How can I speed up saving the subquery results? This is just one step in a series of filtering stages in an internal DB for reporting. Both tables are InnoDB.

Subquery explain results:

explain results

Update

The solution was to use composite indices on both tables. 

-- Add the composite indices
ALTER TABLE `etm` ADD INDEX (`cityCode`, `tvlDate`);
ALTER TABLE `f2avail` ADD INDEX (`cityCode`, `inventoryDate`);

The query went from ~90s to about 6s.

Best Answer

Your screen images leave out details that would show up in SHOW CREATE TABLE. But I will make some guesses.

You need, but don't have, a "composite" INDEX(cityCode, tvlDate) on etm and/or a similar index on the other table.

Other issues:

  • Comparing a DATE to a DATETIME may not work as you expect. If the DATETIME has any time other than 00:00:00, it will not match any DATE.
  • Why mention etm when all the data comes from the other table?
  • Be consistent on definitions -- I'm looking inconsistent lengths on citycode.

Back to the question... Perhaps nothing was cached in RAM when the 90s run was done. Try each query twice.

Related Question