bstjean wrote:
Can you post the table structure, the result of SHOW INDEX for the
tables involved and the EXPLAIN for this query?
Thank you!
Here's the info you wanted to see. Thanks.
table references for EXPLAIN:
regdsubevt2006 as rs
subevent2006 as se
category2006 as ct
participant2006 as p
captain2006 as c
team2006 as t
registration2006 as r
subregtype as st
EXPLAIN
table type possible_keys key key_len ref rows Extra
rs ALL 927 Using where; Using temp
orary;
Using filesort
se eq_ref PRIMARY PRIMARY 2 rs.rel
subevent
ID 1
ct ALL 5
p ALL 1244
c ALL 23
t ALL 15
r ALL 907 Using where
st eq_ref PRIMARY PRIMARY 2 se.rel
subreg
typeID 1
STRUCTURE and SHOW INDEX
all table are type=MyISAM
participant2006
`participantID` bigint(8) unsigned NOT NULL auto_increment,
`fname` varchar(25) NOT NULL default '',
`lname` varchar(25) NOT NULL default '',
`dob` date NOT NULL default '0000-00-00',
`age` tinyint(2) unsigned NOT NULL default '0',
`gender` char(2) NOT NULL default '',
`address` varchar(40) NOT NULL default '',
`city` varchar(35) NOT NULL default '',
`relprovstateID` char(2) NOT NULL default '',
`relcountryID` char(2) NOT NULL default '',
`postalzip` varchar(11) NOT NULL default '',
`dayphone` varchar(12) NOT NULL default '',
`evephone` varchar(12) NOT NULL default '',
`fax` varchar(12) NOT NULL default '',
`email` varchar(40) NOT NULL default '',
`shirt` char(3) NOT NULL default '',
`displayname` char(2) NOT NULL default 'Y',
`acknowrisk` char(2) NOT NULL default '1',
PRIMARY KEY (`participantID`)
Non_unique - 0
Key_name - PRIMARY
Seq_in_index - 1
Column_name - participantID
Collation - A
Cardinality - 1244
Sub_part
Packed
Null
Index_type - BTREE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
captain2006
`captainID` smallint(3) unsigned NOT NULL auto_increment,
`relparticipantID` bigint(8) unsigned NOT NULL default '0',
PRIMARY KEY (`captainID`)
Non_unique - 0
Key_name - PRIMARY
Seq_in_index - 1
Column_name - captainID
Collation - A
Cardinality - 23
Sub_part
Packed
Null
Index_type - BTREE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
team2006
`teamID` smallint(3) unsigned NOT NULL auto_increment,
`teamname` varchar(50) NOT NULL default '',
`relcaptainID` smallint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`teamID`)
Non_unique - 0
Key_name - PRIMARY
Seq_in_index - 1
Column_name - teamID
Collation - A
Cardinality - 15
Sub_part
Packed
Null
Index_type - BTREE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
registration2006
`registrationID` smallint(4) unsigned NOT NULL auto_increment,
`regdatetime` datetime NOT NULL default '0000-00-00 00:00:00',
`relparticipantID` bigint(8) NOT NULL default '0',
PRIMARY KEY (`registrationID`)
Non_unique - 0
Key_name - PRIMARY
Seq_in_index - 1
Column_name - registrationID
Collation - A
Cardinality - 907
Sub_part
Packed
Null
Index_type - BTREE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
regdsubevt2006
`regdsubevtID` smallint(5) unsigned NOT NULL auto_increment,
`relregistrationID` smallint(4) unsigned NOT NULL default '0',
`relsubeventID` smallint(4) unsigned NOT NULL default '0',
PRIMARY KEY (`regdsubevtID`)
Non_unique - 0
Key_name - PRIMARY
Seq_in_index - 1
Column_name - regdsubevtID
Collation - A
Cardinality - 927
Sub_part
Packed
Null
Index_type - BTREE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
subevent2006
`subeventID` smallint(4) unsigned NOT NULL auto_increment,
`releventID` smallint(3) unsigned NOT NULL default '0',
`regtype` char(1) NOT NULL default '',
`relsubregtypeID` char(2) NOT NULL default '',
`earlybirdfee` smallint(3) unsigned NOT NULL default '0',
`earlybirdend` date NOT NULL default '0000-00-00',
`regularfee` smallint(3) unsigned NOT NULL default '0',
`regularend` date NOT NULL default '0000-00-00',
`latefee` smallint(3) unsigned NOT NULL default '0',
`lateend` date NOT NULL default '0000-00-00',
`dayinscost` tinyint(2) unsigned NOT NULL default '0',
`cancelins` tinyint(2) unsigned NOT NULL default '0',
`maxspots` smallint(4) unsigned NOT NULL default '0',
`agemin` tinyint(2) unsigned NOT NULL default '0',
`agemax` tinyint(2) unsigned NOT NULL default '0',
`cutoff` date NOT NULL default '0000-00-00',
`regopen` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`subeventID`)
Non_unique - 0
Key_name - PRIMARY
Seq_in_index - 1
Column_name - subeventID
Collation - A
Cardinality - 22
Sub_part
Packed
Null
Index_type - BTREE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
category2006
`categoryID` smallint(3) unsigned NOT NULL auto_increment,
`categoryname` varchar(20) default NULL,
`relsubeventID` smallint(4) unsigned default '0',
PRIMARY KEY (`categoryID`)
Non_unique - 0
Key_name - PRIMARY
Seq_in_index - 1
Column_name - categoryID
Collation - A
Cardinality - 5
Sub_part
Packed
Null
Index_type - BTREE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
subregtype
`subregtypeID` char(2) NOT NULL default '',
`subregtypename` varchar(20) NOT NULL default '',
PRIMARY KEY (`subregtypeID`)
Non_unique - 0
Key_name - PRIMARY
Seq_in_index - 1
Column_name - subregtypeID
Collation - A
Cardinality - 4
Sub_part
Packed
Null
Index_type - BTREE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~