Bug #44 Optimisation (explain) stage of the normal query takes to long
Submitted: 27 Jan 2003 21:21 Modified: 1 Feb 2003 17:56
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:Any (all)
Assigned to: CPU Architecture:Any

[27 Jan 2003 21:21] Peter Zaitsev 
Description:
Unlike some other reported problems this problem with way too long explain execution, this one happens with rather normal query (number of ranges in the query seems to be small):

mysql>  EXPLAIN SELECT
    ->  ProfessionPCAC.ID AS ActivityCategoryID,ProfessionPCAC.Name as
    -> CategoryName,
    ->  ProfessionPCA.ID as ActivityID,ProfessionPCA.Name as ActivityName,
    ->  Profession.Description,
    ->  Code.Name AS InOutStatus ,
    ->  StaffCode.Name as StaffName ,  StaffCode.ID as StaffCodeID,
    -> StaffCode.Description as StaffDescription ,
    ->  Code01Code.Name as Code01Name ,  Code01Code.ID as Code01CodeID,
    -> Code01Code.Description as Code01Description ,
    ->  Code02Code.Name as Code02Name ,  Code02Code.ID as Code02CodeID,
    -> Code02Code.Description as Code02Description ,
    ->  Code03Code.Name as Code03Name ,  Code03Code.ID as Code03CodeID,
    -> Code03Code.Description as Code03Description ,
    ->  Code04Code.Name as Code04Name ,  Code04Code.ID as Code04CodeID,
    -> Code04Code.Description as Code04Description ,
    ->  Code05Code.Name as Code05Name ,  Code05Code.ID as Code05CodeID,
    -> Code05Code.Description as Code05Description ,
    ->  Code06Code.Name as Code06Name ,  Code06Code.ID as Code06CodeID,
    -> Code06Code.Description as Code06Description ,
    ->  Code07Code.Name as Code07Name ,  Code07Code.ID as Code07CodeID,
    -> Code07Code.Description as Code07Description ,
    ->  Code08Code.Name as Code08Name ,  Code08Code.ID as Code08CodeID,
    -> Code08Code.Description as Code08Description ,
    ->  Code09Code.Name as Code09Name ,  Code09Code.ID as Code09CodeID,
    -> Code09Code.Description as Code09Description ,
    ->  Code10Code.Name as Code10Name ,  Code10Code.ID as Code10CodeID,
    -> Code10Code.Description as Code10Description ,
    ->  Code11Code.Name as Code11Name ,  Code11Code.ID as Code11CodeID,
    -> Code11Code.Description as Code11Description
    ->
    ->  FROM
    ->  (((((((((((((((((ProfessionPCAC INNER JOIN Profession ON
    -> ProfessionPCAC.ProfessionID = Profession.ID)
    ->  INNER JOIN ProfessionPCA ON ProfessionPCAC.ID =
    -> ProfessionPCA.ActivityCategoryID)
    ->  INNER JOIN SSData ON ProfessionPCA.ID = SSData.WorkloadFieldID)
    ->  INNER JOIN SSHeader ON SSData.HeaderID = SSHeader.HeaderID)
    ->  INNER JOIN Code ON SSHeader.SourceOfRequestID = Code.ID)
    ->  INNER JOIN Code StaffCode ON  SSHeader.StaffCodeID = StaffCode.ID)
    ->  INNER JOIN Code Code01Code ON  SSHeader.Code03 = Code01Code.ID)
    ->  INNER JOIN Code Code02Code ON  SSHeader.Code04 = Code02Code.ID)
    ->  INNER JOIN Code Code03Code ON  SSHeader.Code05 = Code03Code.ID)
    ->  INNER JOIN Code Code04Code ON  SSHeader.Code06 = Code04Code.ID)
    ->  INNER JOIN Code Code05Code ON  SSHeader.Code07 = Code05Code.ID)
    ->  INNER JOIN Code Code06Code ON  SSHeader.Code08 = Code06Code.ID)
    ->  INNER JOIN Code Code07Code ON  SSHeader.Code09 = Code07Code.ID)
    ->  INNER JOIN Code Code08Code ON  SSHeader.Code10 = Code08Code.ID)
    ->  INNER JOIN Code Code09Code ON  SSHeader.Code11 = Code09Code.ID)
    ->  INNER JOIN Code Code10Code ON  SSHeader.Code12 = Code10Code.ID)
    ->  INNER JOIN Code Code11Code ON  SSHeader.Code13 = Code11Code.ID)
    ->
    ->  WHERE
    ->  Profession.ID = 11 and  SSData.DateOfService  >= '2002/12/01' and
    -> '2002/12/31' >= SSData.DateOfService
    ->  AND  (SSHeader.SourceOfRequestID = 1  OR SSHeader.SourceOfRequestID = 2 OR
    -> SSHeader.SourceOfRequestID = 3)
    ->  and  ( StaffCodeID > 0 )  AND  ( Code03 > 0 )  AND  ( Code04 > 0 )  AND
    -> ( Code05 > 0 )  AND
    ->  ( Code06 > 0 )  AND  ( Code07 > 0 )  AND  ( Code08 > 0 )  AND  ( Code09 >
    -> 0 )  AND  ( Code10 > 0 )  AND
    ->  ( Code11 > 0 )  AND  ( Code12 > 0 )  AND  ( Code13 > 0 )
    ->
    ->  ORDER BY StaffName ASC, Code01Name ASC, Code02Name ASC, Code03Name ASC,
    -> Code04Name ASC, Code05Name ASC, Code06Name ASC, Code07Name ASC, Code08Name
    -> ASC, Code09Name ASC, Code10Name ASC, Code11Name ASC,
    -> ProfessionPCAC.DisplayOrderSS ASC, ProfessionPCA.DisplayOrderSS ASC
    ->
    -> ;
+----------------+--------+---------------+---------+---------+----------------------------------+------+---------------------------------+
| table          | type   | possible_keys | key     | key_len | ref                              | rows | Extra                           |
+----------------+--------+---------------+---------+---------+----------------------------------+------+---------------------------------+
| Profession     | const  | PRIMARY       | PRIMARY |       4 | const                            |    1 | Using temporary; Using filesort |
| SSData         | ALL    | DateOfService | NULL    |    NULL | NULL                             |   12 | Using where                     |
| ProfessionPCA  | ALL    | PRIMARY       | NULL    |    NULL | NULL                             |    4 | Using where                     |
| ProfessionPCAC | eq_ref | PRIMARY       | PRIMARY |       4 | ProfessionPCA.ActivityCategoryID |    1 | Using where                     |
| SSHeader       | eq_ref | PRIMARY       | PRIMARY |       4 | SSData.HeaderID                  |    1 | Using where                     |
| Code           | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.SourceOfRequestID       |    1 |                                 |
| StaffCode      | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.StaffCodeID             |    1 |                                 |
| Code01Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code03                  |    1 |                                 |
| Code02Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code04                  |    1 |                                 |
| Code03Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code05                  |    1 |                                 |
| Code04Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code06                  |    1 |                                 |
| Code05Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code07                  |    1 |                                 |
| Code06Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code08                  |    1 |                                 |
| Code07Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code09                  |    1 |                                 |
| Code08Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code10                  |    1 |                                 |
| Code09Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code11                  |    1 |                                 |
| Code10Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code12                  |    1 |                                 |
| Code11Code     | eq_ref | PRIMARY       | PRIMARY |       4 | SSHeader.Code13                  |    1 |                                 |
+----------------+--------+---------------+---------+---------+----------------------------------+------+---------------------------------+
18 rows in set (2 min 42.18 sec)

How to repeat:
1) Start MySQL with --lower_case_table_names=1

CREATE TABLE code (
  ID int(11) NOT NULL default '0',
  CodeFieldID int(11) NOT NULL default '0',
  Name varchar(12) NOT NULL default '',
  Active smallint(6) default '0',
  Description varchar(40) default NULL,
  Mutable smallint(6) default '0',
  PRIMARY KEY  (ID)
) TYPE=MyISAM;

--
-- Dumping data for table 'code'
--

INSERT INTO code VALUES (1,3,'I',1,'Inpatient',0),(2,3,'O',1,'Client',0),(3,3,'R',1,'Referred-in',0),(4,3,'A',1,'SR, not uniquely identified',0),(5,3,'F',1,'Facility/Organization',0),(6,3,'T',1,'Resident',0),(7,3,'Q',1,'Quality control',0),(8,3,'S',1,'Callibration standards',0),(9,3,'H',1,'Staff health',0),(10,3,'X',1,'Research',0),(11,3,'E',1,'Environment',0),(12,3,'Z',1,'Not applicable',0),(13,3,'D',1,'Physicians',0),(14,3,'N',1,'Nurses',0),(15,3,'P',1,'Pharmacists',0),(16,3,'M',1,'Other',0),(17,4,'T',1,'Telephone',0),(18,4,'F',1,'Face-to-face',0),(19,4,'N',1,'None',0),(253,1,'Steve',1,'Steve',1),(258,1,'Fu',1,'Fu Manchu',1),(263,1,'John',1,'John Smith',1),(350,322,'one',0,'one-01',1),(352,322,'two',0,'two-01',1),(354,323,'one',0,'one-02',1),(356,323,'two',0,'two-02',1),(358,324,'one',0,'one-03',1),(360,324,'two',0,'two-03',1),(362,325,'one',0,'one-04',1),(364,325,'two',0,'two-04',1),(366,326,'one',0,'one-05',1),(368,326,'two',0,'two-05',1),(370,327,'one',0,'one-06',1),(372,327,'two',0,'two-06',1),(374,328,'one',0,'one-07',1),(376,328,'two',0,'two-07',1),(378,329,'one',0,'one-08',1),(380,329,'two',0,'two-08',1),(382,2,'unit1',0,'unit1',1),(384,2,'unit2',0,'unit2',1),(404,330,'one',0,'one-09',1),(406,330,'two',0,'two-09',1),(408,331,'one',0,'one-10',1),(410,331,'two',0,'two-10',1),(412,332,'one',0,'one-11',1),(414,332,'two',0,'two-11',1),(415,333,'one',0,'one-12',1),(417,333,'two',0,'two-12',1),(418,334,'one',0,'one-13',1),(420,334,'two',0,'two-13',1),(422,335,'one',0,'one-14',1),(424,335,'two',0,'two-14',1);

--
-- Table structure for table 'codefieldname'
--

CREATE TABLE codefieldname (
  ID int(11) NOT NULL default '0',
  Name varchar(15) NOT NULL default '',
  FieldOrder int(11) default NULL,
  Description varchar(30) default NULL,
  DisplayOrder int(11) default NULL,
  SpecialCodeField smallint(6) default NULL,
  PRIMARY KEY  (ID)
) TYPE=MyISAM;

--
-- Dumping data for table 'codefieldname'
--

INSERT INTO codefieldname VALUES (1,'Staff',0,'Staff',0,1),(2,'Unit',0,'Unit',0,1),(3,'SourceOfRequest',0,'SOR',0,1),(4,'Contact',0,'Contact',0,1),(322,'Code01',1,'Code01',1,0),(323,'Code02',2,'Code02',2,0),(324,'Code03',3,'Code03',3,0),(325,'Code04',4,'Code04',4,0),(326,'Code05',5,'Code05',5,0),(327,'Code06',6,'Code06',6,0),(328,'Code07',7,'Code07',7,0),(329,'Code08',8,'Code08',8,0),(330,'Code09',9,'Code09',9,0),(331,'Code10',10,'Code10',10,0),(332,'Code11',11,'Code11',11,0),(333,'Code12',12,'Code12',12,0),(334,'Code13',13,'Code13',13,0),(335,'Code14',14,'Code14',14,0);

--
-- Table structure for table 'profession'
--

CREATE TABLE profession (
  ID int(11) NOT NULL default '0',
  DisplayOrder int(11) NOT NULL default '0',
  Name varchar(15) NOT NULL default '',
  Active smallint(6) NOT NULL default '0',
  Description varchar(30) default NULL,
  PRIMARY KEY  (ID)
) TYPE=MyISAM;

--
-- Dumping data for table 'profession'
--

INSERT INTO profession VALUES (1,1,'AC',0,'Ambulatory Care'),(2,2,'CP',1,'Cardiopulmonary'),(3,3,'RT',0,'Recreational Therapy'),(4,4,'CD',0,'Communication Disorders'),(5,5,'CN',0,'Clinical Nutrition'),(6,6,'OT',1,'Occupational Therapy'),(7,7,'PC',0,'Pastoral Care'),(8,8,'PS',0,'Psychology'),(9,9,'PT',1,'Physiotherapy'),(10,10,'RN',0,'Nursing'),(11,11,'SW',1,'Social Work');

--
-- Table structure for table 'professionpca'
--

CREATE TABLE professionpca (
  ID int(11) NOT NULL default '0',
  Deactivate smallint(6) NOT NULL default '0',
  ProfessionID int(11) NOT NULL default '0',
  ActivityCategoryID int(11) NOT NULL default '0',
  Name varchar(50) NOT NULL default '',
  Weighting float NOT NULL default '1',
  DisplayOrderPS int(11) NOT NULL default '0',
  DisplayOrderSS int(11) NOT NULL default '0',
  SeparatorRow smallint(6) default '0',
  PRIMARY KEY  (ID),
  KEY ProfessionID (ProfessionID)
) TYPE=MyISAM;

--
-- Dumping data for table 'professionpca'
--

INSERT INTO professionpca VALUES (428,0,11,426,'Act1',10,1,1,0),(429,0,11,426,'Act2',10,2,2,0),(430,0,11,427,'Act3',10,3,3,0),(431,0,11,427,'Act4',10,4,4,0);

--
-- Table structure for table 'professionpcac'
--

CREATE TABLE professionpcac (
  ID int(11) NOT NULL default '0',
  Deactivate smallint(6) NOT NULL default '0',
  ProfessionID int(11) NOT NULL default '0',
  ActivityCategory int(11) NOT NULL default '0',
  Name varchar(50) NOT NULL default '',
  Weighting float NOT NULL default '1',
  DisplayOrderPS int(11) NOT NULL default '0',
  DisplayOrderSS int(11) NOT NULL default '0',
  SeparatorRow smallint(6) default '0',
  PRIMARY KEY  (ID)
) TYPE=MyISAM;

--
-- Dumping data for table 'professionpcac'
--

INSERT INTO professionpcac VALUES (426,0,11,1,'AC1',1,1,1,0),(427,0,11,2,'AC2',1,2,2,0);

--
-- Table structure for table 'professionpcp'
--

CREATE TABLE professionpcp (
  ID int(11) NOT NULL default '0',
  Deactivate smallint(6) NOT NULL default '0',
  ProfessionID int(11) NOT NULL default '0',
  ActivityCategoryID int(11) NOT NULL default '0',
  Code varchar(6) NOT NULL default '',
  ProcedureName varchar(50) NOT NULL default '',
  UnitValue float NOT NULL default '1',
  DisplayOrderPS int(11) NOT NULL default '0',
  DisplayOrderSS int(11) NOT NULL default '0',
  SeparatorRow smallint(6) default '0',
  PRIMARY KEY  (ID)
) TYPE=MyISAM;

--
-- Dumping data for table 'professionpcp'
--

--
-- Table structure for table 'psdata'
--

CREATE TABLE psdata (
  ID int(11) NOT NULL default '0',
  EntryValue float NOT NULL default '0',
  HeaderID int(11) NOT NULL default '0',
  WorkloadFieldID int(11) NOT NULL default '0',
  Factor float NOT NULL default '0',
  DateOfService date default NULL,
  PRIMARY KEY  (ID)
) TYPE=MyISAM;

--
-- Dumping data for table 'psdata'
--

INSERT INTO psdata VALUES (461,1,459,428,10,'2002-12-31'),(462,2,459,429,10,'2002-12-31'),(463,3,459,430,10,'2002-12-31'),(464,4,459,431,10,'2002-12-31');

--
-- Table structure for table 'psheader'
--

CREATE TABLE psheader (
  HeaderID int(11) NOT NULL default '0',
  CreationDate date NOT NULL default '0000-00-00',
  ProfessionID int(11) NOT NULL default '0',
  DemographicID int(11) NOT NULL default '0',
  StaffCodeID int(11) NOT NULL default '0',
  UnitID int(11) default NULL,
  EncounterNumberID int(11) default NULL,
  SourceOfRequestID int(11) default NULL,
  ContactID int(11) default NULL,
  Code01 int(11) default NULL,
  Code02 int(11) default NULL,
  Code03 int(11) default NULL,
  Code04 int(11) default NULL,
  Code05 int(11) default NULL,
  Code06 int(11) default NULL,
  Code07 int(11) default NULL,
  Code08 int(11) default NULL,
  Code09 int(11) default NULL,
  Code10 int(11) default NULL,
  Code11 int(11) default NULL,
  Code12 int(11) default NULL,
  Code13 int(11) default NULL,
  Code14 int(11) default NULL,
  PRIMARY KEY  (HeaderID)
) TYPE=MyISAM;

--
-- Dumping data for table 'psheader'
--

INSERT INTO psheader VALUES (459,'2002-12-31',11,456,258,0,0,1,18,350,356,358,364,366,372,376,380,406,408,412,415,420,424);

--
-- Table structure for table 'ssdata'
--

CREATE TABLE ssdata (
  ID int(11) NOT NULL default '0',
  HeaderID int(11) NOT NULL default '0',
  WorkloadFieldID int(11) NOT NULL default '0',
  Factor float NOT NULL default '0',
  EntryValue float NOT NULL default '0',
  DateOfService date default NULL,
  PRIMARY KEY  (ID),
  KEY DateOfService (DateOfService)
) TYPE=MyISAM;

--
-- Dumping data for table 'ssdata'
--

INSERT INTO ssdata VALUES (470,468,428,10,20,'2002-12-30'),(472,468,429,10,6,'2002-12-30'),(474,468,430,10,8,'2002-12-30'),(476,468,431,10,9,'2002-12-30'),(480,478,428,10,4,'2002-12-30'),(482,478,429,10,6,'2002-12-30'),(484,478,430,10,9,'2002-12-30'),(486,478,431,10,7,'2002-12-30'),(505,503,428,10,15,'2002-12-27'),(507,503,429,10,6,'2002-12-27'),(509,503,430,10,8,'2002-12-27'),(511,503,431,10,4,'2002-12-27');

--
-- Table structure for table 'ssheader'
--

CREATE TABLE ssheader (
  HeaderID int(11) NOT NULL default '0',
  CreationDate date NOT NULL default '0000-00-00',
  ProfessionID int(11) NOT NULL default '0',
  StaffCodeID int(11) NOT NULL default '0',
  UnitID int(11) default NULL,
  SourceOfRequestID int(11) default NULL,
  ContactID int(11) default NULL,
  Code01 int(11) default NULL,
  Code02 int(11) default NULL,
  Code03 int(11) default NULL,
  Code04 int(11) default NULL,
  Code05 int(11) default NULL,
  Code06 int(11) default NULL,
  Code07 int(11) default NULL,
  Code08 int(11) default NULL,
  Code09 int(11) default NULL,
  Code10 int(11) default NULL,
  Code11 int(11) default NULL,
  Code12 int(11) default NULL,
  Code13 int(11) default NULL,
  Code14 int(11) default NULL,
  PRIMARY KEY  (HeaderID)
) TYPE=MyISAM;

--
-- Dumping data for table 'ssheader'
--

INSERT INTO ssheader VALUES (468,'2002-12-31',11,258,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),(478,'2002-12-31',11,258,0,1,18,0,0,0,0,0,0,0,0,0,0,0,0,0,0),(503,'2002-12-31',11,258,0,1,18,350,356,360,364,366,372,376,378,404,408,412,415,420,422);

 EXPLAIN SELECT
 ProfessionPCAC.ID AS ActivityCategoryID,ProfessionPCAC.Name as
CategoryName,
 ProfessionPCA.ID as ActivityID,ProfessionPCA.Name as ActivityName,
 Profession.Description,
 Code.Name AS InOutStatus ,
 StaffCode.Name as StaffName ,  StaffCode.ID as StaffCodeID,
StaffCode.Description as StaffDescription ,
 Code01Code.Name as Code01Name ,  Code01Code.ID as Code01CodeID,
Code01Code.Description as Code01Description ,
 Code02Code.Name as Code02Name ,  Code02Code.ID as Code02CodeID,
Code02Code.Description as Code02Description ,
 Code03Code.Name as Code03Name ,  Code03Code.ID as Code03CodeID,
Code03Code.Description as Code03Description ,
 Code04Code.Name as Code04Name ,  Code04Code.ID as Code04CodeID,
Code04Code.Description as Code04Description ,
 Code05Code.Name as Code05Name ,  Code05Code.ID as Code05CodeID,
Code05Code.Description as Code05Description ,
 Code06Code.Name as Code06Name ,  Code06Code.ID as Code06CodeID,
Code06Code.Description as Code06Description ,
 Code07Code.Name as Code07Name ,  Code07Code.ID as Code07CodeID,
Code07Code.Description as Code07Description ,
 Code08Code.Name as Code08Name ,  Code08Code.ID as Code08CodeID,
Code08Code.Description as Code08Description ,
 Code09Code.Name as Code09Name ,  Code09Code.ID as Code09CodeID,
Code09Code.Description as Code09Description ,
 Code10Code.Name as Code10Name ,  Code10Code.ID as Code10CodeID,
Code10Code.Description as Code10Description ,
 Code11Code.Name as Code11Name ,  Code11Code.ID as Code11CodeID,
Code11Code.Description as Code11Description

 FROM
 (((((((((((((((((ProfessionPCAC INNER JOIN Profession ON
ProfessionPCAC.ProfessionID = Profession.ID)
 INNER JOIN ProfessionPCA ON ProfessionPCAC.ID =
ProfessionPCA.ActivityCategoryID)
 INNER JOIN SSData ON ProfessionPCA.ID = SSData.WorkloadFieldID)
 INNER JOIN SSHeader ON SSData.HeaderID = SSHeader.HeaderID)
 INNER JOIN Code ON SSHeader.SourceOfRequestID = Code.ID)
 INNER JOIN Code StaffCode ON  SSHeader.StaffCodeID = StaffCode.ID)
 INNER JOIN Code Code01Code ON  SSHeader.Code03 = Code01Code.ID)
 INNER JOIN Code Code02Code ON  SSHeader.Code04 = Code02Code.ID)
 INNER JOIN Code Code03Code ON  SSHeader.Code05 = Code03Code.ID)
 INNER JOIN Code Code04Code ON  SSHeader.Code06 = Code04Code.ID)
 INNER JOIN Code Code05Code ON  SSHeader.Code07 = Code05Code.ID)
 INNER JOIN Code Code06Code ON  SSHeader.Code08 = Code06Code.ID)
 INNER JOIN Code Code07Code ON  SSHeader.Code09 = Code07Code.ID)
 INNER JOIN Code Code08Code ON  SSHeader.Code10 = Code08Code.ID)
 INNER JOIN Code Code09Code ON  SSHeader.Code11 = Code09Code.ID)
 INNER JOIN Code Code10Code ON  SSHeader.Code12 = Code10Code.ID)
 INNER JOIN Code Code11Code ON  SSHeader.Code13 = Code11Code.ID)

 WHERE
 Profession.ID = 11 and  SSData.DateOfService  >= '2002/12/01' and
'2002/12/31' >= SSData.DateOfService
 AND  (SSHeader.SourceOfRequestID = 1  OR SSHeader.SourceOfRequestID = 2 OR
SSHeader.SourceOfRequestID = 3)
 and  ( StaffCodeID > 0 )  AND  ( Code03 > 0 )  AND  ( Code04 > 0 )  AND
( Code05 > 0 )  AND
 ( Code06 > 0 )  AND  ( Code07 > 0 )  AND  ( Code08 > 0 )  AND  ( Code09 >
0 )  AND  ( Code10 > 0 )  AND
 ( Code11 > 0 )  AND  ( Code12 > 0 )  AND  ( Code13 > 0 )

 ORDER BY StaffName ASC, Code01Name ASC, Code02Name ASC, Code03Name ASC,
Code04Name ASC, Code05Name ASC, Code06Name ASC, Code07Name ASC, Code08Name
ASC, Code09Name ASC, Code10Name ASC, Code11Name ASC,
ProfessionPCAC.DisplayOrderSS ASC, ProfessionPCA.DisplayOrderSS ASC
[28 Jan 2003 17:01] MySQL Developer 
Put on hold as this is not a bug and is not likely to be fixed in the near future.
[1 Feb 2003 17:56] MySQL Developer 
This is normal behaviour when number of columns / keys/ 
tables/ ON clauses / WHERE clauses is very big.

This is not a bug as MySQL optimiser has to go through
all possible combo's in find_best() function.

It is possible therefore that query is executed much faster
on small tables when indices are dropped, as this stage
would be skipped.

The only thing that can be done in future, in 4.1
is to add a possibility to kill a thread when it is 
running in find_best() function.