MySQL UNION ALL Performance

Question

I've got two subqueries that I'm unioning together. Both subqueries return in < 200 ms but when I union them I get a query that runs in about 6 seconds (union all). The first query returns about 1000000 rows while the second only returns 15 or so. Does anybody have any advice on what I could do to speed this up (without changing the data model (with the exception of indexes)) ? Maybe something to do with Collation ?

Additional information. If I limit that first query to return, say , 50K results the union is much faster... around 400 ms. So, it seems to be tied to the size of the first query.

Here is an example of the query...

(
SELECT 
    assign_.AssociatedObjectID AS repID, 
    ent_.MVStoreID AS StoreID
FROM MVAssignment AS assign_ 
JOIN MVMasterEntity_AttachedStores AS ent_
    ON ent_.MVStoreID = assign_.AssignmentValue 
WHERE assign_.AssociatedObjectID IN (7416) 
AND assign_.AssociatedObjectClass = 'MVRep' 
AND assign_.AssignmentSubType IN ('StoreID', 'StoreNumberID') 
AND assign_.IsDeleted = 0
) 

UNION all

( 
SELECT 
    assign_.AssociatedObjectID AS repID, 
    ent_.MVStoreID AS StoreID
FROM MVAssignment AS assign_ 
JOIN `MVCustomFieldValue` AS cfv_
    ON cfv_.`Value` = assign_.AssignmentValue 
JOIN MVMasterEntity_AttachedStores AS ent_
    ON ent_.MVStoreID = cfv_.EntityID 
WHERE assign_.AssociatedObjectID IN (7303,7304,7305,7306,7307,9116,9117,
                                    9118,9119,9120,9121,9122,9123) 
AND assign_.AssociatedObjectClass = 'MVRep' 
AND assign_.AssignmentSubType = 'CustomFieldValue' 
AND assign_.IsDeleted = 0
)

and related table show create table sql

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') DEFAULT 'MVCustomField',
  "AssignmentSubType"
      enum('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 'BrandID',
  "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',
  PRIMARY KEY ("ID"),
  KEY "ClassName" ("ClassName"),
  KEY "AssignmentValue" ("AssignmentValue"),
  KEY "AssociatedObjectID" ("AssociatedObjectID"),
  KEY "OwnerObjectID" ("OwnerObjectID"),
  KEY "AssociatedObject" ("AssociatedObjectClass","AssociatedObjectID"),
  KEY "OwnerObject" ("OwnerObjectClass","OwnerObjectID"),
  KEY "assignmentQuery" ("AssignmentValue","AssociatedObjectClass",
                         "IsDeleted","AssignmentSubType")
);

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")
);

CREATE TABLE "MVCustomFieldValue" (
  "ID" int(11) NOT NULL AUTO_INCREMENT,
  "ClassName" enum('MVCustomFieldValue') DEFAULT 'MVCustomFieldValue',
  "Created" datetime DEFAULT NULL,
  "LastEdited" datetime DEFAULT NULL,
  "Value" varchar(510) DEFAULT NULL,
  "CustomFieldID" int(11) NOT NULL DEFAULT '0',
  "EntityID" int(11) NOT NULL DEFAULT '0',
  "combined" varchar(600) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY ("ID"),
  KEY "CustomFieldID" ("CustomFieldID"),
  KEY "EntityID" ("EntityID"),
  KEY "ClassName" ("ClassName"),
  KEY "CFEntity" ("CustomFieldID","EntityID"),
  KEY "CFValue" ("CustomFieldID","Value"(255)),
  KEY "CFEntityValue" ("CustomFieldID","EntityID","Value"(255)),
  KEY "combined" ("combined"(255)),
  KEY "combinedEntity" ("combined"(255),"EntityID")
);

Explain from the query:

1   PRIMARY <derived7>  NULL    ALL NULL    NULL    NULL    NULL    1188    100.00  Using temporary; Using filesort
1   PRIMARY creatorEntity   NULL    eq_ref  PRIMARY PRIMARY 4   creatorAssignment.OwnerObjectID 1   100.00  NULL
1   PRIMARY <derived2>  NULL    ref <auto_key0> <auto_key0> 4   creatorAssignment.AssociatedObjectID    815 100.00  NULL
7   DERIVED _asst   NULL    range   newAssQOne,newAssQTwo,AssociatedObjectClass,newAssThree,AssociatedObjectID,AssociatedObject,rJamesIndex newAssThree 7   NULL    3818    100.00  Using where; Using index
2   DERIVED ent_    NULL    index   MVStoreID   MVStoreID   4   NULL    14079   100.00  Using index; Using temporary; Using filesort
2   DERIVED cfv_    NULL    ref EntityID,combined   combined    4   impulsetest.ent_.MVStoreID  20  100.00  Using where; Using index
2   DERIVED ass_    NULL    ref assignmentQuery,newAssQOne,newAssQTwo,AssociatedObjectClass,newAssThree,AssignmentValue,AssociatedObjectID,AssociatedObject,rJamesIndex assignmentQuery 773 impulsetest.cfv_.combined,const,const,const 10  30.61   Using index condition; Using where
3   UNION   ass_    NULL    range   assignmentQuery,newAssQOne,newAssQTwo,AssociatedObjectClass,newAssThree,AssignmentValue,AssociatedObjectID,AssociatedObject,rJamesIndex newAssQOne  9   NULL    16  100.00  Using index condition; Using where; Using temporary; Using filesort
3   UNION   ent_    NULL    ref MVStoreID   MVStoreID   4   impulsetest.ass_.AssignmentValue    1   100.00  Using where; Using index
4   UNION   ass_    NULL    range   assignmentQuery,newAssQOne,newAssQTwo,AssociatedObjectClass,newAssThree,AssignmentValue,AssociatedObjectID,AssociatedObject,rJamesIndex newAssQOne  9   NULL    139 100.00  Using index condition; Using where; Using temporary; Using filesort
4   UNION   ent_    NULL    ref MVStoreID   MVStoreID   4   impulsetest.ass_.AssignmentValue    1   100.00  Using where; Using index
5   UNION   ass_    NULL    ref assignmentQuery,newAssQOne,newAssQTwo,AssociatedObjectClass,newAssThree,AssignmentValue,AssociatedObjectID,AssociatedObject,rJamesIndex newAssQOne  9   const,const,const,const 1   100.00  Using where; Using temporary; Using filesort
5   UNION   list_stores NULL    ref MVListID,MVStoreID,MVStoreID_2,MVStoreID_3,MVStoreID_4,MVStoreID_5,MVStoreID_6,MVStoreID_7,MVStoreID_8,MVStoreID_9,MVStoreID_10,MVStoreID_11,MVStoreID_12,MVStoreID_13,MVStoreID_14,MVStoreID_15,MVStoreID_16   MVListID    4   impulsetest.ass_.AssignmentValue    679 100.00  Using index condition
5   UNION   ent_    NULL    ref MVStoreID   MVStoreID   4   impulsetest.list_stores.MVStoreID   1   100.00  Using index
6   UNION   ass_    NULL    range   assignmentQuery,newAssQOne,newAssQTwo,AssociatedObjectClass,newAssThree,AssignmentValue,AssociatedObjectID,AssociatedObject,rJamesIndex newAssQOne  9   NULL    141 100.00  Using index condition; Using where; Using temporary; Using filesort
6   UNION   sto_    NULL    ref PRIMARY,RetailerID  RetailerID  4   impulsetest.ass_.AssignmentValue    68  100.00  Using where; Using index
6   UNION   ent_    NULL    ref MVStoreID   MVStoreID   4   impulsetest.sto_.ID 1   100.00  Using index

Answer 1

WHERE assign_.AssociatedObjectID IN (7416,...) 
  AND assign_.AssociatedObjectClass = 'MVRep' 
  AND assign_.AssignmentSubType IN ('StoreID', 'StoreNumberID') 
  AND assign_.IsDeleted = 0

is problematical since you have two multi-valued IN clauses. Suggest you have both of these in order to cover the optimizable cases:

INDEX(AssociatedObjectClass, IsDeleted, AssociatedObjectID, AssignmentSubType)
INDEX(AssociatedObjectClass, IsDeleted, AssignmentSubType, AssociatedObjectID)

(The column order is important.) The first index will do nicely for the first subquery. Ditto for second:second.

You can remove MVMasterEntity_AttachedStores from the first subquery (unless you need to check for the existence of the row in that table).

Some other issues:

INDEX(a),  INDEX(a,b)

Get rid of the former since the latter serves the same uses.

INDEX(x(255), y)

Prefix indexes are virtually useless. In this case, y will never be looked at. So don't bother to go beyond INDEX(x,255)).

When timing queries, be sure to avoid the Query cache. One way is to

SELECT SQL_NO_CACHE ...

I suspect your million-row fetch that took only 200ms was really coming from the QC, whereas it was being re-evaluated when it was in the UNION.

If you want to discuss further, provide EXPLAIN SELECT ... for any selects being discussed.

Answer 2

Change your first Query with this one ( I think the problem on JOIN between varchar and int ) Also you are using assign_.AssociatedObjectID IN (7416) while you only have one record to check change it to = operator

SELECT 
    assign_.AssociatedObjectID AS repID, 
    ent_.MVStoreID AS StoreID
FROM MVAssignment AS assign_ 
JOIN MVMasterEntity_AttachedStores AS ent_ ON ent_.MVStoreID = cast(assign_.AssignmentValue as int(11))
WHERE assign_.AssociatedObjectID = 7416
AND assign_.AssociatedObjectClass = 'MVRep' 
AND assign_.AssignmentSubType IN ('StoreID', 'StoreNumberID') 
AND assign_.IsDeleted = 0