Speed up creation of mysql table (from select)

Question

I've got a query that runs in about 1.6 seconds and returns 150Kish rows. I have been building a memory engine based temp table from this query and this operation takes about 6.5 seconds. I've sifted through lots of documentation on the mysql site as well as futzed with indexes on the temp table (hardly any difference at all when I have indexes vs not having them) trying to find some way for the table creation to be a bit better performing. Does anybody have any suggestions with respect to config options that may speed up creating an in memory temp table (or non temp table)? To clear up what exactly I'm looking for (I think) is some sort of table config options that may increase the speed of the creation. I've gone through most of the ones listed on the mysql 5.7 site but nothing seems to change the time it takes to create the temp/standard table in memory.

CREATE TEMPORARY TABLE 
IF NOT EXISTS myTempTable 
(ugID INT,stID INT, INDEX st (`st`) ) 
ENGINE=MEMORY AS 
select ugID, stID 
from a_complex_query_that_runs_in_under_2_seconds_but_takes_6_to_write_to_a_temptable_with_150k_rows


id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived15> ALL NULL    NULL    NULL    NULL    559 
1   PRIMARY creatorEntity   eq_ref  PRIMARY PRIMARY 4   creatorAssignment.OwnerObjectID 1   Using index
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    152580  Using where; Using join buffer
15  DERIVED VMAssignment    ref AssociatedObjectID,AssociatedObject AssociatedObject    2       2902    Using where
2   DERIVED test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    214 Using where; Using temporary; Using filesort
2   DERIVED cfv_    ref CustomFieldID,EntityID,CFEntity,CFValue,CFEntityValue   CustomFieldID   4   testCloud.test_.AssignmentValue 4232    Using where
2   DERIVED ent_    ref VMStoreID   VMStoreID   4   testCloud.cfv_.EntityID 1   Using index
3   UNION   test_   ref AssociatedObjectID,AssociatedObject AssociatedObject    2       2902    Using where; Using temporary; Using filesort
3   UNION   cfv_    ref CustomFieldID,EntityID,CFEntity,CFValue,CFEntityValue   CFValue 772 func,func   4   Using where
3   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.cfv_.EntityID 1   Using index
4   UNION   test_   const   PRIMARY PRIMARY 4       1   Using index
5   UNION   test_   index_merge AssociatedObjectID,AssociatedObject AssociatedObjectID,AssociatedObject 4,6 NULL    1   Using intersect(AssociatedObjectID,AssociatedObject); Using where; Using index; Using temporary; Using filesort
5   UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where
5   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
6   UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where; Using temporary; Using filesort
6   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
6   UNION   test_   ref AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssignmentValue 768 testCloud.entity_.State 1   Using where
7   UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where; Using temporary; Using filesort
7   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
7   UNION   test_   ref AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssignmentValue 768 testCloud.entity_.Zip   1   Using where
8   UNION   test_   range   AssociatedObjectID,AssociatedObject AssociatedObjectID  4   NULL    150 Using where; Using temporary; Using filesort
8   UNION   entity_ ref PRIMARY,ClassName,Address   Address 456 func,func   4   Using where
8   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
9   UNION   test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    203 Using where; Using temporary; Using filesort
9   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.test_.AssignmentValue 1   Using where; Using index
10  UNION   test_   const   PRIMARY PRIMARY 4       1   Using index
11  UNION   test_   ref AssociatedObjectID,AssociatedObject AssociatedObjectID  4       1   Using where; Using temporary; Using filesort
11  UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where
11  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
12  UNION   test_   ref AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObject    2       2902    Using where; Using temporary; Using filesort
12  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.test_.AssignmentValue 1   Using where; Using index
13  UNION   test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    239 Using where; Using temporary; Using filesort
13  UNION   list_stores ref VMListID,VMStoreID,VMStoreID_2,VMStoreID_3,VMStoreID_4,VMStoreID_5,VMStoreID_6,VMStoreID_7,VMStoreID_8,VMStoreID_9,VMStoreID_10,VMStoreID_11,VMStoreID_12,VMStoreID_13,VMStoreID_14,VMStoreID_15,VMStoreID_16   VMListID    4   testCloud.test_.AssignmentValue 318 Using where
13  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.list_stores.VMStoreID 1   Using index
14  UNION   test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    70  Using where; Using temporary; Using filesort
14  UNION   sto_    ref PRIMARY,RetailerID  RetailerID  4   testCloud.test_.AssignmentValue 63  Using where; Using index
14  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.sto_.ID   1   Using index
NULL    UNION RESULT    <union2,3,4,5,6,7,8,9,10,11,12,13,14>   ALL NULL    NULL    NULL    NULL    NULL    

Answer 1

There is not much there to work with. (SHOW CREATE TABLE and EXPLAIN) But I do see one thing:

Using intersect(AssociatedObjectID,AssociatedObject)

That can almost always be improved by using a 'composite' index:

INDEX(AssociatedObjectID, AssociatedObject)

There may be other cases for composite indexes, but we would need to see the query.

"but the time that it takes the query to populate the temp table" -- Creating the 152K rows is likely to take longer than inserting that many rows.

Try ENGINE=InnoDB instead of ENGINE=MEMORY. InnoDB has made performance strides.

Also try not including INDEX st (st) in the CREATE, but adding it afterwards. Building the index incrementally may be the slowest part of the CREATE.

There are probably no tunables that affect performance of MEMORY.