many left joins; missing key?!?

webnick

Thanks for any advise!

SELECT applications.RowId AS aRowId, applications.RowVersion AS aRowVersion, applications.OwnedBy AS aOwnedBy, applications.Created AS aCreated, applications.ModifiedBy AS aModifiedBy, applications.Modified AS aModified, applications.company AS acompany, applications.job AS ajob, applications.candidate AS acandidate, applications.recruiter AS arecruiter, applications.court AS acourt, applications.status AS astatus, applications.hold AS ahold, applications.priority AS apriority, applications.resume AS aresume, applications.comments AS acomments, applications.amark AS amark, applications.Q AS Q, applications.Qtype AS Qtype, applications.Qname AS Qname, applications.Qsize AS Qsize, companies.Company, jobs.jobName, candidates.firstName AS firstName, candidates.lastName AS lastName, candidates.preferredEmail AS preferredEmail, recruiters.Name, applicationxcourt.court, applicationxstatus.`status` , applicationxpriority.quality, applicationxhold.`status`
FROM `applications`
INNER JOIN candidates ON applications.candidate = candidates.RowId
LEFT JOIN companies ON applications.company = companies.RowId
LEFT JOIN jobs ON applications.job = jobs.RowId
LEFT JOIN recruiters ON applications.recruiter = recruiters.RowId
LEFT JOIN applicationxcourt ON applications.court = applicationxcourt.RowId
LEFT JOIN applicationxstatus ON applications.`status` = applicationxstatus.RowId
LEFT JOIN applicationxpriority ON applications.priority = applicationxpriority.RowId
LEFT JOIN applicationxhold ON applications.hold = applicationxhold.RowId
WHERE (
(
applications.company =1
OR 1 = -1
OR 1 =1
OR ( 1 = jobs.community
AND jobs.community <>1 )
)
)
ORDER BY `aCreated` DESC , `company` ASC , `job` ASC
LIMIT 0 , 30

--EXPLAIN
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	applications 	ALL 	candidate 	NULL 	NULL 	NULL 	22761 	Using temporary; Using filesort
1 	SIMPLE 	candidates 	eq_ref 	PRIMARY,RowId 	PRIMARY 	4 	agilerec_new3.applications.candidate 	1
1 	SIMPLE 	companies 	eq_ref 	PRIMARY,RowId 	PRIMARY 	4 	agilerec_new3.applications.company 	1
1 	SIMPLE 	jobs 	eq_ref 	PRIMARY,RowId 	PRIMARY 	4 	agilerec_new3.applications.job 	1
1 	SIMPLE 	recruiters 	eq_ref 	PRIMARY,RowId 	PRIMARY 	4 	agilerec_new3.applications.recruiter 	1
1 	SIMPLE 	applicationxcourt 	eq_ref 	PRIMARY,RowId 	PRIMARY 	4 	agilerec_new3.applications.court 	1
1 	SIMPLE 	applicationxstatus 	eq_ref 	PRIMARY,RowId 	PRIMARY 	4 	agilerec_new3.applications.status 	1
1 	SIMPLE 	applicationxpriority 	eq_ref 	PRIMARY,RowId 	PRIMARY 	4 	agilerec_new3.applications.priority 	1
1 	SIMPLE 	applicationxhold 	eq_ref 	PRIMARY,RowId 	PRIMARY 	4 	agilerec_new3.applications.hold 	1

--
-- Table structure for table `applications`
--

CREATE TABLE `applications` (
  `RowId` int(11) NOT NULL auto_increment,
  `RowVersion` int(11) default NULL,
  `OwnedBy` varchar(255) default NULL,
  `Created` datetime default NULL,
  `ModifiedBy` varchar(255) default NULL,
  `Modified` datetime default NULL,
  `company` int(11) default NULL,
  `job` int(11) default NULL,
  `candidate` int(11) default NULL,
  `comments` longtext,
  `status` int(11) NOT NULL default '8',
  `priority` int(11) NOT NULL default '8',
  `court` int(11) NOT NULL default '9',
  `resume` longtext,
  `hold` int(11) NOT NULL default '1',
  `recruiter` int(11) NOT NULL default '1',
  `amark` bigint(20) unsigned default '0',
  `Q` longblob,
  `Qtype` varchar(50) default NULL,
  `Qname` varchar(80) default NULL,
  `Qsize` int(11) NOT NULL default '0',
  `aflags` int(11) NOT NULL default '0',
  PRIMARY KEY  (`RowId`),
  UNIQUE KEY `RowId` (`RowId`),
  KEY `job` (`job`),
  KEY `candidate` (`candidate`),
  KEY `company` (`company`),
  KEY `status` (`status`),
  KEY `priority` (`priority`),
  KEY `court` (`court`),
  FULLTEXT KEY `comments` (`comments`),
  FULLTEXT KEY `resume` (`resume`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 10240 kB' AUTO_INCREMENT=25978 ;

-- --------------------------------------------------------

--
-- Table structure for table `candidates`
--

CREATE TABLE `candidates` (
  `RowId` int(11) NOT NULL auto_increment,
  `RowVersion` int(11) default NULL,
  `OwnedBy` varchar(255) default NULL,
  `Created` datetime default NULL,
  `ModifiedBy` varchar(255) default NULL,
  `Modified` datetime default NULL,
  `firstName` varchar(255) default NULL,
  `lastName` varchar(255) default NULL,
  `address1` varchar(255) default NULL,
  `address2` varchar(255) default NULL,
  `preferredEmail` varchar(255) NOT NULL default '',
  `altEmail` varchar(255) default NULL,
  `workPhone` varchar(13) default NULL,
  `cellPhone` varchar(13) default NULL,
  `homePhone` varchar(13) default NULL,
  `resume` longtext,
  `comments` longtext,
  `source` int(11) default '1',
  `City` varchar(255) default NULL,
  `StateProvince` varchar(255) default NULL,
  `PostalCode` varchar(255) default NULL,
  `Country` varchar(255) default NULL,
  `assignedTo` int(11) default NULL,
  `active` tinyint(4) default '1',
  `Gender` int(11) default NULL,
  `Ethnicity` int(11) default NULL,
  `Birthdate` date default NULL,
  `futurelook` tinyint(4) default '1',
  `sourceable` tinyint(4) default '1',
  `WebPageURL` varchar(255) default NULL,
  `Password` varchar(255) default NULL,
  `companysee` set('-2','-1','0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60') default NULL,
  `userlevel` tinyint(4) default '1',
  `mark` bigint(20) unsigned default '0',
  `community` int(11) default '1',
  `communitysee` set('-2','-1','0','1','2','3','4','5','6','7','8','9','10','11','12','13') default '1',
  `company` int(11) default '0',
  `cvtype` varchar(11) default NULL,
  `cvsize` int(11) default NULL,
  `cvname` varchar(50) default NULL,
  `cv` longblob,
  `precision` varchar(7) default 'TBD',
  `latitude` double(11,6) default NULL,
  `longitude` double(11,6) default NULL,
  `addressg` varchar(255) default NULL,
  `cityg` varchar(255) default NULL,
  `stateg` char(2) default NULL,
  `zipp4` varchar(10) default NULL,
  `countryg` char(2) default NULL,
  PRIMARY KEY  (`RowId`),
  UNIQUE KEY `RowId` (`RowId`),
  KEY `fi0` (`OwnedBy`),
  KEY `fi1` (`ModifiedBy`),
  KEY `fi3` (`assignedTo`),
  KEY `fi9` (`Ethnicity`),
  KEY `fi10` (`Gender`),
  KEY `fi13` (`source`),
  KEY `company` (`company`),
  KEY `lastName` (`lastName`),
  KEY `preferredEmail` (`preferredEmail`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`),
  FULLTEXT KEY `resume` (`resume`),
  FULLTEXT KEY `com` (`comments`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18550 ;

Sxooter

I'm note sure what you're asking. Did this query get an error message? If so, what was it? Just the subject? Or did it have some context we're missing here?

webnick

The query seems to run slow (3-5 seconds) the first time I run it and then drop to .01 for tries right after that.

I'm wondering how to optimize this better?? Some of the WHEREs. But it looks like no index is being used for the first part of the query. That one also has a 'filesort' and is a required order.

Is it possible to recreate the index on date-created for descending? Maybe GROUP BY would help the sub results. Thanks for any help...

Sxooter

webnick wrote:

Thanks for any advise!

SELECT applications.RowId AS aRowId,
 applications.RowVersion AS aRowVersion,
 applications.OwnedBy AS aOwnedBy,
-- rest of select list deleted for brevity
FROM `applications` 
INNER JOIN candidates ON applications.candidate = candidates.RowId
LEFT JOIN companies ON applications.company = companies.RowId
LEFT JOIN jobs ON applications.job = jobs.RowId
LEFT JOIN recruiters ON applications.recruiter = recruiters.RowId
LEFT JOIN applicationxcourt ON applications.court = applicationxcourt.RowId
LEFT JOIN applicationxstatus ON applications.`status` = applicationxstatus.RowId
LEFT JOIN applicationxpriority ON applications.priority = applicationxpriority.RowId
LEFT JOIN applicationxhold ON applications.hold = applicationxhold.RowId
WHERE (
(
applications.company =1
OR 1 = -1
OR 1 =1
OR ( 1 = jobs.community
AND jobs.community <>1 )
)
)
ORDER BY `aCreated` DESC , `company` ASC , `job` ASC
LIMIT 0 , 30

OK, I'm not sure what exactly you're trying to do, so maybe you can explain what you're trying to do with this query.

Are the left joins all necessary? The problem with left joins is that the query planner likely can't change order of joins, so if there's a more efficient way to do it, it can't just figure it out. btw, the latest version of pgsql can reorder outer joins, as can oracle and a few other dbs. The query planner in mysql isn't so smart, which can be a good AND a bad thing.

If you write a smart query, knowing how your data is laid out, then MySQL can do ok but it can't think for you at all.

A few odd things in this query:

WHERE 
  (
    (
    applications.company =1
    OR 1 = -1  -- this will never be true
    OR 1 =1  --  this will always be true
    OR 
      (  -- this will ALWAYS be true.  unless jobs.community is null.  

      1 = jobs.community  -- If you want is not null, just use "is not null"
      AND                      --  But it won't matter if you use "is not null" here 
      jobs.community <>1  -- because the or 1=1 will override it anyway.
    )
  )
)

given the order of your ORs and ANDs up there, you will get every single row. So, just drop the where clause.

Also, you're inconsistently quoting identifiers i.e. applications. Either quote everything all the time, or don't quote it at all. mixing it is a bad thing that might cause breakage moving from one server / db to another.