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
Best Answer
is problematical since you have two multi-valued
IN
clauses. Suggest you have both of these in order to cover the optimizable cases:(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:
Get rid of the former since the latter serves the same uses.
Prefix indexes are virtually useless. In this case,
y
will never be looked at. So don't bother to go beyondINDEX(x,255))
.When timing queries, be sure to avoid the Query cache. One way is to
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.