bKY9ygCq

1CREATE TEMPORARY TABLE 
2IF NOT EXISTS myTempTable 
3    (ugID INT,stID INT, INDEX st (`st`) ) 
4ENGINE=MEMORY AS 
5select 
6    ugID, 
7    stID 
8from 
9    a_complex_query_that_runs_in_under_2_seconds_but_takes_6_to_write_to_a_temptable_with_150k_rows
10	
11id  select_type table   type    possible_keys   key key_len ref rows    Extra
121   PRIMARY <derived15> ALL NULL    NULL    NULL    NULL    559 
131   PRIMARY creatorEntity   eq_ref  PRIMARY PRIMARY 4   creatorAssignment.OwnerObjectID 1   Using index
141   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    152580  Using where; Using join buffer
1515  DERIVED VMAssignment    ref AssociatedObjectID,AssociatedObject AssociatedObject    2       2902    Using where
162   DERIVED test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    214 Using where; Using temporary; Using filesort
172   DERIVED cfv_    ref CustomFieldID,EntityID,CFEntity,CFValue,CFEntityValue   CustomFieldID   4   testCloud.test_.AssignmentValue 4232    Using where
182   DERIVED ent_    ref VMStoreID   VMStoreID   4   testCloud.cfv_.EntityID 1   Using index
193   UNION   test_   ref AssociatedObjectID,AssociatedObject AssociatedObject    2       2902    Using where; Using temporary; Using filesort
203   UNION   cfv_    ref CustomFieldID,EntityID,CFEntity,CFValue,CFEntityValue   CFValue 772 func,func   4   Using where
213   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.cfv_.EntityID 1   Using index
224   UNION   test_   const   PRIMARY PRIMARY 4       1   Using index
235   UNION   test_   index_merge AssociatedObjectID,AssociatedObject AssociatedObjectID,AssociatedObject 4,6 NULL    1   Using intersect(AssociatedObjectID,AssociatedObject); Using where; Using index; Using temporary; Using filesort
245   UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where
255   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
266   UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where; Using temporary; Using filesort
276   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
286   UNION   test_   ref AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssignmentValue 768 testCloud.entity_.State 1   Using where
297   UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where; Using temporary; Using filesort
307   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
317   UNION   test_   ref AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssignmentValue 768 testCloud.entity_.Zip   1   Using where
328   UNION   test_   range   AssociatedObjectID,AssociatedObject AssociatedObjectID  4   NULL    150 Using where; Using temporary; Using filesort
338   UNION   entity_ ref PRIMARY,ClassName,Address   Address 456 func,func   4   Using where
348   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
359   UNION   test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    203 Using where; Using temporary; Using filesort
369   UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.test_.AssignmentValue 1   Using where; Using index
3710  UNION   test_   const   PRIMARY PRIMARY 4       1   Using index
3811  UNION   test_   ref AssociatedObjectID,AssociatedObject AssociatedObjectID  4       1   Using where; Using temporary; Using filesort
3911  UNION   entity_ ref PRIMARY,ClassName   ClassName   2       23326   Using where
4011  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.entity_.ID    1   Using index
4112  UNION   test_   ref AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObject    2       2902    Using where; Using temporary; Using filesort
4212  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.test_.AssignmentValue 1   Using where; Using index
4313  UNION   test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    239 Using where; Using temporary; Using filesort
4413  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
4513  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.list_stores.VMStoreID 1   Using index
4614  UNION   test_   range   AssignmentValue,AssociatedObjectID,AssociatedObject,assignmentQuery AssociatedObjectID  4   NULL    70  Using where; Using temporary; Using filesort
4714  UNION   sto_    ref PRIMARY,RetailerID  RetailerID  4   testCloud.test_.AssignmentValue 63  Using where; Using index
4814  UNION   ent_    ref VMStoreID   VMStoreID   4   testCloud.sto_.ID   1   Using index
49NULL    UNION RESULT    <union2,3,4,5,6,7,8,9,10,11,12,13,14>   ALL NULL    NULL    NULL    NULL    NULL