Slow CREATE table from subquery using SELECT .. INNER JOIN 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:



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.
Looking on Your pictures, I can guess - for run subquery You also use MySQLWorkbench. Most of software - not return to You back all rows from dataset, by default it filter up to 1000 records (some less)

So select without ORDER BY start work and immediately stop after first XXX rows.

In case of create table:


The subquery return all rows
Insert operation always longer than SELECT ... much, much longer


Possible or not increase speed of insert? it depends from Your current server settings and configuration - may yes, may be not

Also it depend from other server loading:


Select it non-locking operations, it start work immediately 
INSERT FROM SELECT (other form of CREATE from SELECT) - lock all data, so if other transactions running on this time, it spend some time for lock tables


Also You can test query with combined indexes on both tables:
(cityCode,tvlDate) and (cityCode,inventoryDate)