Mysql – Speed Difference between these two queries

MySQL

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?

Best Answer

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.

Related Question