Mine

explain SELECT DISTINCT SUBSTRING_INDEX(`OS_Ver`,'(',1)+0 AS OSVersion, COUNT(DISTINCT deviceId) AS DevCount, COUNT(title) As PsirtCount FROM `PLCChassis` LEFT JOIN `PSIRT` USING (`assessmentId`,`deviceID`) GROUP BY OSVersion;
+----+-------------+------------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | PLCChassis | ALL  | NULL          | NULL | NULL    | NULL | 21775 | Using temporary; Using filesort | 
|  1 | SIMPLE      | PSIRT      | ALL  | NULL          | NULL | NULL    | NULL |  2698 |                                 | 
+----+-------------+------------+------+---------------+------+---------+------+-------+---------------------------------+

PLCChassis | CREATE TABLE `PLCChassis` (
  `assessmentId` varchar(20) NOT NULL,
  `deviceID` int(11) unsigned NOT NULL auto_increment,
  `IP_Address` varchar(25) default NULL,
  `OS_Name` varchar(40) default NULL,
  `OS_Ver` varchar(128) default NULL,
  `Hostname` varchar(256) default NULL,
  `Vendor` varchar(32) default NULL,
  `Product_Model` varchar(128) default NULL,
  `Serial_Number` varchar(32) default NULL,
  `FEoS` varchar(160) default NULL,
  `EoS` date default NULL,
  `EoSWM` date default NULL,
  `EoCR` date default NULL,
  `LDoS` date default NULL,
  `ContractNumber` varchar(12) default NULL,
  `BeginDate` date default NULL,
  `EndDate` date default NULL,
  `ROMVersion` varchar(128) default NULL,
  `UpTime` varchar(128) default NULL,
  `ImageFile` varchar(128) default NULL,
  `TotalProcMemory` varchar(32) default NULL,
  `FlashMemory` varchar(16) default NULL,
  UNIQUE KEY `uniqrow` (`assessmentId`,`deviceID`),
  KEY `assessmentId_2` (`assessmentId`),
  KEY `deviceID` (`deviceID`)
) ENGINE=MyISAM AUTO_INCREMENT=119978 DEFAULT CHARSET=latin1;

PSIRT | CREATE TABLE `PSIRT` (
  `assessmentId` varchar(20) NOT NULL,
  `deviceID` int(11) unsigned NOT NULL,
  `Title` varchar(80) default NULL,
  `URL` varchar(256) default NULL,
  `Status` varchar(16) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1