[SOLVED] Optimising query with subqueries

[tidalik]

I have a query that works, but desperately needs to be optimised.

 

MySQL Ver: 5.0.45

The statement finds the number offspring of a nominated Sire or Dam who themselves have offspring.  In this case the nominated Sire or Dam is 1093.

The table index is PedigreeId, SireId/DamId are the PedigreeId of the Sire/Dam.

Table has at present 13 000 entries.

 

The query:

select count(PedigreeId) from peds where (SireId = 1093 OR DamId = 1093) 
AND (PedigreeId IN (select SireId from peds) OR PedigreeId IN (Select DamId from peds))

As you can see, selecting all the SireId and DamId in the entire db is overkill - but I don't know how best to select the PedigreeId who are either sires or dams.  The query works, it is just very slow.

 

Table structure:

CREATE TABLE `peds` (
  `PedigreeId` int(11) NOT NULL auto_increment,
  `Name` varchar(64) default NULL,
  `SireId` int(11) default NULL,
  `DamId` int(11) default NULL,
  `Sex` varchar(10) default NULL,
  `DOB` date default NULL,
  `PreTitle` varchar(32) default NULL,
  `PostTitle` varchar(32) default NULL,
  `RegNo` varchar(64) default NULL,
  `Color` varchar(64) default NULL,
  `Photo` varchar(200) default NULL,
  `COI` varchar(64) default NULL,
  `COO` varchar(64) default NULL,
  PRIMARY KEY  (`PedigreeId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

Edited to stop query scrolling

[tidalik]

Found the answer myself.

 

The only way was to rewrite it as a join, went from running at 1.88 secs down to 0.31 secs

 

select count(DISTINCT p1.PedigreeId) from peds p1, peds p2 where (p1.SireId = 3023 OR p1.DamId = 3023) AND (p1.PedigreeId = p2.SireId OR p1.PedigreeId = p2.DamId)