0

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 ?

  • also tried creating in innodb. That was slower... 18 seconds. – Josh Jun 20 '18 at 16:24
  • I take back the comment about the super simple select taking the same amount of time. I just ran it again and it took .01 seconds. – Josh Jun 20 '18 at 16:53
  • I also changed the query so that I was getting back 20K is records. 15 seconds to create... – Josh Jun 20 '18 at 18:49
  • What are the slowlog column headings? In particular, what are 8048999 and 1548456745? I'll guess one is "rows examined". How many rows in each table? – Rick James Jun 21 '18 at 18:15
  • Some indexes are redundant. When you have INDEX(a) and INDEX(a,b), drop of the former. Drop joshTest and AssociatedObjectID and AssignmentValue. (This won't solve the problem; it's just a cleanup.) – Rick James Jun 21 '18 at 18:22
0

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

  • Hi Rick. Thanks for you help. I added the index but it didn't help performance. The explain is still the same. I have the max_heap_table_size set to SET SESSION max_heap_table_size=134217728000; which I think should be plenty big (not getting the table is full error). I've added both the slow query log and the table explain to my original post. – Josh Jun 21 '18 at 13:38
  • Here is something else interesting. I can take out the join to the MVMasterEntity_AttachedStores table and the temp table creation takes 8 seconds but examines almost 50% more rows... 12Million – Josh Jun 21 '18 at 14:01
  • @Josh - See the additions I made. – Rick James Jun 21 '18 at 20:08
  • Thanks Rick. I'll make those adjustments and see what happens. Question for you... how could you tell from the explain that null was being tested ? – Josh Jun 22 '18 at 1:32
  • Ok, I made the adjustments but I'm still stuck @ 15 seconds. I also rearranged the query so that the join on the TTT... table turned into an exists in the where statement. That got the optimizer to use the primary key you suggested and also got it off of the first thing the optimizer is looking at. still 15 seconds. though. Is it possible that something in this query is 'going to disk' and I'm just not seeing it in any of the debug tools I've used so far. Any other mysql settings that I could be missing ? What about me selecting from two tables... does that == no covering index? – Josh Jun 22 '18 at 3:45

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.