mysql memory temp table creation SLOW

Question

I'm trying to get to the bottom of what I'm thinking is a performance issue when creating in memory temporary tables. The underlying query I have looks to me to be pretty well optimized. The explain is below the query. The temporary table it builds has ~4Million rows. The query (with no cache specified) runs in 150 ms, the creation of the table takes 15 seconds. Couple other interesting things I'm seeing. I'd thought maybe I needed a covering index for the underlying query that builds the table but don't think that's really possible when I'm selecting from two tables that are joined so I replaced the query with a simpler version, just selecting the StoreID. Run time was the same ... ~15 seconds to build the temp table. After the table was created I did a "create table like " and then did the index build. It took about 2 seconds which also doesn't make sense to me. Anyone got any thoughts?

CREATE TEMPORARY TABLE IF NOT EXISTS TTTe5f0f36ca5fec1d49a75d5cb5d88233f 
        (repID INT,StoreID INT,EntityID INT , INDEX st (`StoreID`) ) engine=memory
         AS 
SELECT  ass_.AssociatedObjectID AS repID, ass_.OwnerUsergroupID AS EntityID,
        ent_.MVStoreID AS StoreID, 1 as assignmentCount
    FROM  MVAssignment AS ass_
    JOIN  TTTf122b1fd3249346930db2dff0c43b022 existsTT 
                ON existsTT.ID = ass_.AssociatedObjectID
    JOIN  MVList_Stores as list_stores
                ON ass_.AssignmentValue = list_stores.MVListID
    JOIN  MVMasterEntity_AttachedStores ent_
                ON ent_.MVStoreID = list_stores.MVStoreID
    WHERE  ass_.AssociatedObjectClass = 'MVRep'
      AND  ass_.AssignmentSubType = 'ListID'
      AND  ass_.IsDeleted = 0
      AND  EXISTS 
    (
        SELECT  StoreID
            from  TTT08d81ab1ab02c079ffebae286c342e62 existsTT
            where  ent_.MVStoreID=existsTT.StoreID
    ) ;
/* queryTime - 15.1777 seconds */ 

The explain is as follows

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY existsTT    ALL ID              20024   Using where
1   PRIMARY ass_    ref assignmentQuery,joshTest,testTwo,joshTest3,anodaTEst,uniqueness,uutt,AssignmentValue,AssociatedObjectID,AssociatedObject    testTwo 4   benchmark.existsTT.ID   1   Using index condition
1   PRIMARY list_stores ref StoreList,MVListID,MVStoreID    MVListID    4   benchmark.ass_.AssignmentValue  1   Using index condition
1   PRIMARY ent_    ref MVStoreID   MVStoreID   4   benchmark.list_stores.MVStoreID 1   Using where; Using index
2   DEPENDENT SUBQUERY  existsTT    ref st  st  5   benchmark.ent_.MVStoreID    2   

Here is the json explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "existsTT",
          "access_type": "ALL",
          "possible_keys": [
            "ID"
          ],
          "rows": 20024,
          "filtered": 100,
          "attached_condition": "(\"benchmark\".\"existsTT\".\"ID\" is not null)"
        }
      },
      {
        "table": {
          "table_name": "ass_",
          "access_type": "ref",
          "possible_keys": [
            "assignmentQuery",
            "joshTest",
            "testTwo",
            "joshTest3",
            "anodaTEst",
            "uniqueness",
            "uutt",
            "AssignmentValue",
            "AssociatedObjectID",
            "AssociatedObject"
          ],
          "key": "testTwo",
          "used_key_parts": [
            "AssociatedObjectID"
          ],
          "key_length": "4",
          "ref": [
            "benchmark.existsTT.ID"
          ],
          "rows": 1,
          "filtered": 100,
          "index_condition": "(((\"benchmark\".\"ass_\".\"IsDeleted\" = 0) and (\"benchmark\".\"ass_\".\"AssociatedObjectClass\" = 'MVRep') and (\"benchmark\".\"ass_\".\"AssignmentSubType\" = 'ListID')) and (\"benchmark\".\"ass_\".\"AssignmentValue\" is not
        }
      },
 {
        "table": {
          "table_name": "list_stores",
          "access_type": "ref",
          "possible_keys": [
            "StoreList",
            "MVListID",
            "MVStoreID"
          ],
          "key": "MVListID",
          "used_key_parts": [
            "MVListID"
          ],
          "key_length": "4",
          "ref": [
            "benchmark.ass_.AssignmentValue"
          ],
          "rows": 1,
          "filtered": 100,
          "index_condition": "(\"benchmark\".\"ass_\".\"AssignmentValue\" = \"benchmark\".\"list_stores\".\"MVListID\")"
        }
      },
      {
        "table": {
          "table_name": "ent_",
          "access_type": "ref",
          "possible_keys": [
            "MVStoreID"
          ],
          "key": "MVStoreID",
          "used_key_parts": [
            "MVStoreID"
          ],
          "key_length": "4",
          "ref": [
            "benchmark.list_stores.MVStoreID"
          ],
          "rows": 1,
          "filtered": 100,
          "using_index": true,
          "attached_condition": "exists(/* select#2 */ select \"benchmark\".\"existsTT\".\"StoreID\" from \"benchmark\".\"TTT08d81ab1ab02c079ffebae286c342e62\" \"existsTT\" where (\"benchmark\".\"ent_\".\"MVStoreID\" = \"benchmark\".\"existsTT\".\"StoreID\"
          "attached_subqueries": [
            {
              "dependent": true,
              "cacheable": false,
              "query_block": {
                "select_id": 2,
                "table": {
                  "table_name": "existsTT",
                  "access_type": "ref",
                  "possible_keys": [
                    "st"
                  ],
                  "key": "st",
                  "used_key_parts": [
                    "StoreID"
                  ],
                  "key_length": "5",
                  "ref": [
                    "benchmark.ent_.MVStoreID"
                  ],
"rows": 2,
                  "filtered": 100
                }
              }
            }
          ]
        }
      }
    ]
  }
} 

show create table syntax for all involved tables:

CREATE TABLE "MVAssignment" (
  "ID" int(11) NOT NULL AUTO_INCREMENT,
  "ClassName" enum('MVAssignment') DEFAULT 'MVAssignment',
  "Created" datetime DEFAULT NULL,
  "LastEdited" datetime DEFAULT NULL,
  "AssignmentType" enum('MVCustomField','MVBrand','MVStore','MVList','MVRetailer','MVBillingCode','MVRep','MVTagName','MVSkillAndCapability') DEFAULT 'MVCustomField',
  "AssignmentSubType" enum('SkillsAndCapabilities','SkillAndCapabilityID','allSkillsAndCapabilities','BrandID','Brands','allBrands','CustomFields','CustomFieldNameID','CustomFieldValue','allCustomFields','BillingCodes','BillingCodeID','allBillingCodes','Lists','ListID','allLists','RepID','allReps','Reps','RepsLetterRange','Stores','StoresLetterRange','StoresState','StoresZip','StoreZip','StoresCity','StoreID','allStores','StoreZips','StoreZipsRange','allStoreZips','StoresByNumber','StoreNumberID','allStoresByNumber','Retailers','RetailersLetterRange','RetailerID','allRetailers','Tags','TagNameID','TagValue','allTags') DEFAULT 'SkillsAndCapabilities',
  "AssignmentValue" varchar(255) DEFAULT NULL,
  "AssociatedObjectClass" enum('MVmPlan','MVRep','MVSalesToolBucket','MVUsergroup','MVAlert','MVItemLevelOverride') DEFAULT 'MVmPlan',
  "AssociatedObjectID" int(11) NOT NULL DEFAULT '0',
  "IsDeleted" tinyint(1) unsigned NOT NULL DEFAULT '0',
  "OwnerObjectClass" enum('MVEntityUser') DEFAULT 'MVEntityUser',
  "OwnerObjectID" int(11) NOT NULL DEFAULT '0',
  "OwnerUsergroupID" int(11) NOT NULL DEFAULT '0',
  "UniquenessHash" varchar(32) DEFAULT NULL,
  PRIMARY KEY ("ID"),
  KEY "OwnerObject" ("OwnerObjectClass","OwnerObjectID"),
  KEY "assignmentQuery" ("AssignmentValue","AssociatedObjectClass","IsDeleted","AssignmentSubType"),
  KEY "joshTest" ("AssignmentValue","AssociatedObjectID","AssociatedObjectClass","AssignmentSubType","IsDeleted"),
  KEY "testTwo" ("AssociatedObjectID","AssignmentValue","AssignmentSubType","AssociatedObjectClass","IsDeleted"),
  KEY "joshTest3" ("AssignmentValue","AssociatedObjectID","AssociatedObjectClass","AssignmentSubType","IsDeleted","OwnerObjectID"),
  KEY "anodaTEst" ("AssociatedObjectID","AssociatedObjectClass","AssignmentSubType","IsDeleted","AssignmentValue"),
  KEY "uniqueness" ("IsDeleted","AssociatedObjectClass","AssignmentType","AssociatedObjectID","UniquenessHash"),
  KEY "uutt" ("AssociatedObjectID","AssignmentValue","AssignmentSubType","AssociatedObjectClass","IsDeleted","UniquenessHash"),
  KEY "AssignmentValue" ("AssignmentValue"),
  KEY "AssociatedObjectID" ("AssociatedObjectID"),
  KEY "OwnerObjectID" ("OwnerObjectID"),
  KEY "AssociatedObject" ("AssociatedObjectClass","AssociatedObjectID"),
  KEY "ClassName" ("ClassName"),
  KEY "rickJames" ("AssociatedObjectClass","AssignmentSubType","IsDeleted")
)

CREATE TEMPORARY TABLE "TTT97a84e6f7eaa4bf3b2fbb8491517324c" (
  "ID" int(11) DEFAULT NULL,
  KEY "ID" ("ID")
)

CREATE TABLE "MVList_Stores" (
  "ID" int(11) NOT NULL AUTO_INCREMENT,
  "MVListID" int(11) NOT NULL DEFAULT '0',
  "MVStoreID" int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY ("ID"),
  KEY "StoreList" ("MVStoreID","MVListID"),
  KEY "MVListID" ("MVListID"),
  KEY "MVStoreID" ("MVStoreID")
)

CREATE TABLE "MVMasterEntity_AttachedStores" (
  "ID" int(11) NOT NULL AUTO_INCREMENT,
  "MVMasterEntityID" int(11) NOT NULL DEFAULT '0',
  "MVStoreID" int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY ("ID"),
  KEY "MVMasterEntityID" ("MVMasterEntityID"),
  KEY "MVStoreID" ("MVStoreID")
)

The slow query log for the create table syntax is the following

2018-06-21 13:32:25 benchmark[benchmark] @  [10.0.0.214]    00:00:15    00:00:00    0   8048999 benchmark   0   0   1548456745
/* queryTime - 0.8239 seconds */
↵↵CREATE TEMPORARY TABLE IF NOT EXISTS TTT4611cfe97d6fc08d6242f403e3317551 (repID INT,StoreID INT,EntityID INT , INDEX st (`StoreID`) ) engine=memory AS
SELECT ass_.AssociatedObjectID AS repID, ass_.OwnerUsergroupID AS EntityID, ent_.MVStoreID AS StoreID/*, count(ass_.AssociatedObjectID) as assignmentCount*/ , 1 as assignmentCount FROM MVAssignment AS ass_ JOIN TTT97a84e6f7eaa4bf3b2fbb8491517324c existsTT on existsTT.ID = ass_.AssociatedObjectID JOIN MVList_Stores as list_stores ON ass_.AssignmentValue = list_stores.MVListID JOIN MVMasterEntity_AttachedStores ent_
   ON ent_.MVStoreID = list_stores.MVStoreID
 WHERE ass_.AssociatedObjectClass = 'MVRep'
   AND ass_.AssignmentSubType = 'ListID'
   AND ass_.IsDeleted = 0
/* GROUP BY ass_.AssociatedObjectID, ent_.MVStoreID*/   42994

I've rearranged the query a little bit simplified it a bit for testing (takes 7 seconds but only one column is selected / created on the temp table)...

SELECT  mvas.MVStoreID AS StoreID
    FROM  MVAssignment AS ass_
    JOIN  MVList_Stores AS list_stores  ON ass_.AssignmentValue = list_stores.MVListID
    JOIN  MVMasterEntity_AttachedStores mvas  ON mvas.MVStoreID = list_stores.MVStoreID
    WHERE  EXISTS (
        SELECT  ID
            FROM  TTT97a84e6f7eaa4bf3b2fbb8491517324c existsTT
            WHERE  existsTT.ID = ass_.AssociatedObjectID
                  )
      AND  ass_.AssociatedObjectClass = 'MVRep'
      AND  ass_.AssignmentSubType = 'ListID'
      AND  ass_.IsDeleted = 0 ;

slightly different explain

1   PRIMARY ass_    ref assignmentQuery,joshTest,joshTest3,uniqueness,AssignmentValue,AssociatedObject,rickJames    uniqueness  2   const,const 27457   Using index condition; Using where
1   PRIMARY list_stores ref StoreList,MVListID,MVStoreID    MVListID    4   benchmark.ass_.AssignmentValue  114 Using index condition
1   PRIMARY mvas    ref MVStoreID   MVStoreID   4   benchmark.list_stores.MVStoreID 1   Using index
2   DEPENDENT SUBQUERY  existsTT    eq_ref  PRIMARY PRIMARY 4   benchmark.ass_.AssociatedObjectID   1   NULL

So, I would normally be thinking , well this is as fast as I can get it ... but if I do a super simple query like this...

CREATE TEMPORARY TABLE IF NOT EXISTS TTT4611rcfe97ed6fc08d6242f403e3d3r1r74d5dd51 (
    StoreID INT NOT NULL
) engine=memory AS
SELECT MVStoreID FROM  MVMasterEntity_AttachedStores ent_;

which puts 4 million rows in a temp table in < 1 sec ... it makes me think there is some way I can arrange my query so that it builds the temp table in a similar time that it takes to run the underlying query. Should I give it up ?

Answer 1

Did you set this suitable large before the CREATE?

SET max_heap_table_size = ...;

15 seconds to read 4 million rows is not necessarily "slow".

If you had the slowlog turned on, "Rows examined" would be an interesting number.

Your table ass_ needs this composite index (in any order):

INDEX(AssociatedObjectClass, AssignmentSubType, IsDeleted)

If that is not sufficient, please provide the new EXPLAIN and SHOW CREATE TABLE for each table.

More

Since the Optimizer seems determined to start with existsTT, my index is not useful.

(Please don't reuse the alias existsTT; it is confusing.)

If the IDs in TTT97a84e6f7eaa4bf3b2fbb8491517324c are distinct, please replace its index with PRIMARY KEY(ID).

Many columns are NULLable. And the EXPLAIN seems to be doing tests on such. Consider changing most columns to be NOT NULL