0

Scenario

The following two queries return the same results as desired. The "SubmissionStatus" table is expected to grow to 100K+ rows

Query 1

SELECT field1, field2, ect...        
FROM (SELECT *
    FROM `Job1111`.SubmissionStatus ss
    WHERE ss.fk_ActivityStatus IN (5,12)
    AND ss.ActivityType IN (60,61,62,63,64,65,66,67,68,69,96)) ss       

JOIN `Job1111`.SteelTrackerDetails std
  ON std.BidID = ss.fk_BidIDAssigned

LEFT JOIN `Job1111`.SteelShake stls
  ON ss.ActivityType = 61 AND ss.ActivityRowId = stls.ID

LEFT JOIN `Job1111`.SteelErect stle
  ON ss.ActivityType = 62 AND ss.ActivityRowId = stle.ID

LEFT JOIN `Job1111`.SteelBoltOut stlb
  ON ss.ActivityType = 63 AND ss.ActivityRowId = stlb.ID

LEFT JOIN `Job1111`.SteelImp stli
  ON ss.ActivityType = 64 AND ss.ActivityRowId = stli.ID

LEFT JOIN `Job1111`.SteelWeld stlw
  ON ss.ActivityType = 68 AND ss.ActivityRowId = stlw.ID

LEFT JOIN `Job1111`.SteelSell stlsell
  ON ss.ActivityType = 65 AND ss.ActivityRowId = stlsell.ID

LEFT JOIN `Job1111`.SteelDemo stld
  ON ss.ActivityType = 67 AND ss.ActivityRowId = stld.ID

LEFT JOIN `Job1111`.SteelMisc stlm
  ON ss.ActivityType = 96 AND ss.ActivityRowId = stlm.ID

GROUP BY std.BidID

Query 2

SELECT field1, field2, ect...        
FROM `Job1111`.SubmissionStatus ss       

JOIN `Job1111`.SteelTrackerDetails std
  ON std.BidID = ss.fk_BidIDAssigned

LEFT JOIN `Job1111`.SteelShake stls
  ON ss.ActivityType = 61 AND ss.ActivityRowId = stls.ID

LEFT JOIN `Job1111`.SteelErect stle
  ON ss.ActivityType = 62 AND ss.ActivityRowId = stle.ID

LEFT JOIN `Job1111`.SteelBoltOut stlb
  ON ss.ActivityType = 63 AND ss.ActivityRowId = stlb.ID

LEFT JOIN `Job1111`.SteelImp stli
  ON ss.ActivityType = 64 AND ss.ActivityRowId = stli.ID

LEFT JOIN `Job1111`.SteelWeld stlw
  ON ss.ActivityType = 68 AND ss.ActivityRowId = stlw.ID

LEFT JOIN `Job1111`.SteelSell stlsell
  ON ss.ActivityType = 65 AND ss.ActivityRowId = stlsell.ID

LEFT JOIN `Job1111`.SteelDemo stld
  ON ss.ActivityType = 67 AND ss.ActivityRowId = stld.ID

LEFT JOIN `Job1111`.SteelMisc stlm
  ON ss.ActivityType = 96 AND ss.ActivityRowId = stlm.ID

WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (60,61,62,63,64,65,66,67,68,69,96)
GROUP BY std.BidID

Explain Select (per request)

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'PRIMARY', '<derived12>', 'ref', '<auto_key0>', '<auto_key0>', '5', 'Job1111.std.BidID', '10', NULL
'12', 'DERIVED', 'ss', 'ALL', 'fk_SubmissionStatus_trackerDetails_idx,ActivityType,Combo_SSActivityStatus_ActivityType,Combo_SSActivityType_ActivityRowID', NULL, NULL, NULL, '347', 'Using where; Using temporary; Using filesort'
'12', 'DERIVED', 'stls', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'Job1111.ss.ActivityRowId', '1', 'Using where'
'12', 'DERIVED', 'stle', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'Job1111.ss.ActivityRowId', '1', 'Using where'
'12', 'DERIVED', 'stlb', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'Job1111.ss.ActivityRowId', '1', 'Using where'
'12', 'DERIVED', 'std', 'ref', 'BidID', 'BidID', '4', 'Job1111.ss.fk_BidIDAssigned', '1', NULL
'12', 'DERIVED', 'stli', 'ALL', 'PRIMARY', NULL, NULL, NULL, '3', 'Using where; Using join buffer (Block Nested Loop)'
'12', 'DERIVED', 'stlw', 'ALL', 'PRIMARY', NULL, NULL, NULL, '1', 'Using where; Using join buffer (Block Nested Loop)'
'12', 'DERIVED', 'stlsell', 'ALL', 'PRIMARY', NULL, NULL, NULL, '1', 'Using where; Using join buffer (Block Nested Loop)'
'12', 'DERIVED', 'stld', 'ALL', 'PRIMARY', NULL, NULL, NULL, '1', 'Using where; Using join buffer (Block Nested Loop)'
'12', 'DERIVED', 'stlm', 'ALL', 'PRIMARY', NULL, NULL, NULL, '1', 'Using where; Using join buffer (Block Nested Loop)'
'22', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'21', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'20', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'19', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'18', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'17', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'16', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'15', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'14', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'13', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'11', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'10', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'9', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'8', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'7', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'6', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'5', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'4', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'3', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL
'2', 'SUBQUERY', 'SETTINGS', 'const', 'PRIMARY', 'PRIMARY', '98', 'const', '1', NULL

Show Create Table (per request)

'CREATE TABLE `SubmissionStatus` (
  `PSAActivityID` int(11) NOT NULL AUTO_INCREMENT,
  `fk_SubmissionId` int(11) DEFAULT NULL,
  `ActivityType` tinyint(3) DEFAULT NULL,
  `ActivityRowId` int(11) DEFAULT NULL,
  `fk_BidIDAssigned` int(11) DEFAULT NULL,
  `fk_ActivityStatus` int(11) DEFAULT ''1'',
  `fk_LastModifiedBy` mediumint(8) unsigned DEFAULT NULL,
  `LockField` mediumint(8) unsigned DEFAULT NULL,
  `Rev` tinyint(3) unsigned NOT NULL DEFAULT ''0'',
  `TimeStamp` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`PSAActivityID`),
  KEY `fk_StatusSubmissionId_idx` (`fk_SubmissionId`),
  KEY `fk_tblSubmissionStatus_tblUserPSA3_idx` (`fk_LastModifiedBy`),
  KEY `fk_SubmissionStatus_trackerDetails_idx` (`fk_BidIDAssigned`),
  KEY `TimeStamp` (`TimeStamp`),
  KEY `ActivityType` (`ActivityType`),
  KEY `Combo_SSActivityStatus_ActivityType` (`fk_ActivityStatus`,`ActivityType`),
  KEY `Combo_SSActivityType_ActivityRowID` (`ActivityType`,`ActivityRowId`),
  CONSTRAINT `fk_tblSubmissionStatus_tblSubDetails` FOREIGN KEY (`fk_SubmissionId`) REFERENCES `SubsDetails` (`SubmissionID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2455 DEFAULT CHARSET=utf8'

Question

After review the Explain output it got me thinking about how I was handling the initial call to table SubmissionStatus. But I'm not sure if moving the WHERE statement like I showed above makes a difference. Is one way better then the other?

  • If you want to discuss this further, please explain what a "Job" is and what "SteelXxx" are. The more I look at the query, the less I understand it. – Rick James Oct 29 '16 at 1:43
  • A little explanation... It will be hard to make sense of some of this as you are only currently seeing a small subquery segment of a much larger query. First you can ignore the large number of SubQueries being shown in the explain. All of those are getting values from another table that are normally supplied via variable during runtime... In short they don't happen except for in this test. As far as Job####.SteelXxx. This is a multi-tenancy environment. Hence Job#### Schemas. And SteelXxx are industry specific names for individual tasks being performed... – DarbyM Oct 29 '16 at 2:22
  • In short, these tables are taking data about activities being completed (from one foreign system) and tie these activities to a list of activities that are required to be done. (Generated by another foreign system) I appreciate your feedback, but I feel to be able to make sense of the situation, I would have to restructure the entire question with generic terms and more segmented specifics. – DarbyM Oct 29 '16 at 2:38
0

Your first Query generates a temporary table, then reads it. The second one avoids these steps.

It is almost always better to avoid subqueries when you have a choice. It is less work for the Optimizer, and less work to execute the query.

(There are exceptions.)

This composite index is likely to help:

INDEX(fk_ActivityStatus, ActivityType)

You have LEFT; does that mean you are expecting some of the 'right' queries to return nothing? If not, changing LEFT JOIN to JOIN often (again, there are exceptions) improves things.

  • Thank you for your feedback Rick James. I entirely agree with your viewpoint on the sub query and temporary table being slower. The good news is the second query that does not use the sub query is the one I currently have in place. You did bring up a good point I want to look into further with the composite index. While both the indexes you listed I do have created I did not create a composite between the two. I can imagine that could be quite helpful in the future. – DarbyM Oct 28 '16 at 16:14
  • Update: I implemented 2 combo indexes as you can see.... but when viewing the Execution plan in MySQL Workbench it still shows the table ss as a Full Scan and the included row below from the Examine indicates it is not using the key. Just food for thought – DarbyM Oct 28 '16 at 21:49
  • What percentage of SubmissionStatus is needed? Generally it is more efficient to do a table scan if it exceeds about 20%. Please add SHOW CREATE TABLE SubmissionStatus and EXPLAIN SELECT ... to your question. – Rick James Oct 28 '16 at 22:06
  • The % of SubmissionStatus will continue to grow, as more and more "activities" get there status set to 5 or 12. Realistically this should always be between 70% and 90% Due to artificial creation, current test data shows an unrealistic situation of 22% – DarbyM Oct 28 '16 at 23:01
  • As a side note, I'm using the Left Joins, due to my understanding of Joins as a whole.... My understanding is, in the way I'm using the Joins would act as a cascading effect... Starting with all SubmissionStatus rows, with matching SteelTrackerData Rows Inner joined (that part I'm good with) But then if I use Join on the next row, it would instantly make any of the following joins have no match, because nothing left in the list would have an activityType that was not equal to 61. This understanding was developed via trial and error. Please feel free to tell me I'm wrong... I'm still new. – DarbyM Oct 28 '16 at 23:24

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.