0

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) ?

The create syntax I'm using is below...

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 

Here is an explain from the query that feeds the temp table...

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    
7
  • In my experience, MEMORY tables and TEMPORARY tables are not as beneficial as they sound. How is the execution time if you create it as a "normal" table (engine=Innodb)? How about if you SET GLOBAL innodb_flush_log_at_trx_commit = 2; first (negligibly reduces ACID compliance)?
    – Riedsio
    May 26 '17 at 14:27
  • Creation of the innodb table takes a little longer than the memory one (8.5 secs) and I didn't see any significant boost with the innodb_flush_log_at_trx_commit = 2 setting. I've also tried setting STATS_PERSISTENT=0 in the create but that errors out the create statement
    – Josh
    May 26 '17 at 15:23
  • Once you have created the table, why do you need to recreate it??
    – Rick James
    May 26 '17 at 16:10
  • No indexes on your table? What happens next?
    – Rick James
    May 26 '17 at 16:10
  • There are definitely indexes on my tables from the query that feeds the temp table and the query runs pretty quickly considering all the joining etc that I have to do. I am creating the tmp table as a sort of cte. I did a lot of experimentation with my dataset ... comparing a big query to breaking up the big queries into tmp tables and joining them for the final result. The tmp_table approach is MUCH faster because of the indexes I can use. about 100x faster in most of my tests.
    – Josh
    May 26 '17 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.