Best way I can do this is show the table constructions afterward. My question is why this is taking 7 seconds to run (the tables have about 1900 records in them each)
CREATE TABLE `gf_subcontractors` (
`ID` int(11) unsigned NOT NULL auto_increment,
`Active` tinyint(1) unsigned NOT NULL default '1' COMMENT 'Added 2010-04-07 by Samuel',
`un_username` char(20) default NULL COMMENT 'Unique but null OK',
`Company` char(75) default NULL,
`FirstName` char(35) default NULL,
`MiddleName` char(35) default NULL,
`LastName` char(35) default NULL,
`WriteOnCheckAs` char(128) NOT NULL,
`Race` char(35) NOT NULL COMMENT 'Added 2010-05-01 by Samuel',
`Gender` enum('M','F') default NULL COMMENT 'Added 2010-05-01 by Samuel',
`BirthDate` date default NULL,
`SocSecurityNumber` char(11) default NULL,
`FEIN` char(20) default NULL,
`Address` char(128) NOT NULL,
`City` char(50) NOT NULL,
`State` char(3) NOT NULL,
`Zip` char(10) NOT NULL,
`Phone` char(24) NOT NULL,
`Fax` char(24) NOT NULL,
`Relationship` char(45) NOT NULL,
`Category` char(35) NOT NULL,
`Notes` char(255) NOT NULL,
`CreateDate` datetime NOT NULL,
`Creator` char(20) NOT NULL,
`EditDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`Editor` char(20) NOT NULL,
`LSSS_Ref` char(75) NOT NULL,
`LSSS_OriginalSheet` char(75) NOT NULL,
`LSSS_Index` char(10) NOT NULL,
`LSSS_City` char(25) NOT NULL,
`LSSS_JobTitle` char(75) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `un_username` (`un_username`),
KEY `Company` (`Company`),
KEY `FirstName` (`FirstName`),
KEY `LastName` (`LastName`),
KEY `SocSecurityNumber` (`SocSecurityNumber`),
KEY `Category` (`Category`),
KEY `Active` (`Active`),
KEY `BirthDate` (`BirthDate`),
KEY `Relationship` (`Relationship`)
) ENGINE=InnoDB AUTO_INCREMENT=13001 DEFAULT CHARSET=latin1 COMMENT='Created 2010-03-30 by Samuel'
CREATE TABLE `gf_objects` (
`ID` int(11) NOT NULL auto_increment,
`ParentObject` enum('gf_parents','gf_children','gf_fosterhomes','gf_objects','bais_staff','gf_subcontractors','gf_therapists') NOT NULL,
`Objects_ID` char(20) default NULL,
`Subcontractors_ID` int(11) unsigned default NULL COMMENT '*** Only and always needed for sub->home because gf_objects doesn''t address many-many structure',
`Relationship` char(30) NOT NULL,
`Category` char(25) NOT NULL COMMENT 'Added 2010-03-27 by Samuel',
`FirstName` char(45) default NULL,
`MiddleName` char(35) default NULL,
`LastName` char(45) default NULL,
`BirthDate` date default NULL,
`SocSecurityNumber` char(11) default NULL,
`Gender` enum('M','F') default NULL,
`Race` char(25) default NULL,
`Status` enum('','Pending','Failed','Resolved','Passed') default NULL COMMENT '4 values for CBC',
`PerformingAgency` char(45) default NULL,
`PerformingAgencyPhone` char(24) NOT NULL,
`ReferenceNumber` char(30) NOT NULL,
`EventDate` date default NULL,
`EventEndDate` date default NULL,
`Notes` char(255) NOT NULL,
`CreateDate` datetime NOT NULL,
`Creator` char(20) NOT NULL,
`EditDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`Editor` char(20) NOT NULL,
PRIMARY KEY (`ID`),
KEY `ParentObject` (`ParentObject`),
KEY `Status` (`Status`),
KEY `Category` (`Category`),
KEY `Objects_ID` (`Objects_ID`),
KEY `Relationship` (`Relationship`),
KEY `SocSecurityNumber` (`SocSecurityNumber`),
KEY `Subcontractors_ID` (`Subcontractors_ID`),
KEY `EventDate` (`EventDate`)
) ENGINE=InnoDB AUTO_INCREMENT=32004 DEFAULT CHARSET=latin1 COMMENT='Created 2010-03-22 by Samuel'
CREATE OR REPLACE VIEW _v_TRANS_sub_maxCBC AS
SELECT a.ID, MAX(b.EventDate) AS EventDate
FROM gf_subcontractors a
LEFT JOIN gf_objects b ON a.ID = b.Objects_ID
AND b.Relationship = 'Criminal Background Check'
AND b.ParentObject='gf_subcontractors'
GROUP BY a.IDGROUP BY a.ID
and now the query that is slow:
EXPLAIN
/* get the subcontractor and the last criminal background check using the view as an intermediary */
SELECT
a.ID, a.Active, a.un_username AS UserName, a.Relationship, a.Category, a.FirstName, a.MiddleName, a.LastName, a.Gender, a.Race, a.BirthDate, a.SocSecurityNumber, a.Address, a.City, a.State, a.Zip, a.Phone, a.Fax, a.CreateDate,
COUNT(DISTINCT b.Objects_ID) HomeCount,
c.EventDate AS LastCBC,
d.Status,
d.PerformingAgency,
d.PerformingAgencyPhone,
d.ReferenceNumber,
d.Notes AS CBCNotes
FROM
gf_subcontractors a LEFT JOIN gf_objects b ON a.ID=b.Subcontractors_ID AND b.ParentObject='gf_fosterhomes'
LEFT JOIN _v_TRANS_sub_maxCBC c ON a.ID=c.ID
LEFT JOIN gf_objects d ON c.EventDate=d.EventDate AND d.Objects_ID=a.ID
WHERE a.ID=13000
GROUP BY a.ID
gives the following:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a const PRIMARY PRIMARY 4 const 1
1 PRIMARY b ref ParentObject,Subcontractors_ID Subcontractors_ID 5 const 23
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1990
1 PRIMARY d ref Objects_ID,EventDate EventDate 4 c.EventDate 3
2 DERIVED a index NULL PRIMARY 4 NULL 1990 Using index
2 DERIVED b ref ParentObject,Objects_ID,Relationship ParentObject 1 2012
I am not sure what <derived2> means and don't know how to fix, or what DERIVED means in the last two relationships either. Can anyone help me? Thanks!
