bad query??

Is the problem with the query below in the SELECT?? Should I be using
PHP to do some of things I am trying to get the SELECT to do?

What is happening is it opens 3 or 4 of the same query/process and each
of the process status' stay at 'Copying to tmp table', and pretty much
bring down the server.

Thanks!

SELECT IF(se.regtype = 'i',CONCAT(p.lname,',
',p.fname),t.teamname),p.city,CONCAT(IF(se.regtype='i','Indi vidual','Team'),IF(se.subeventID
>= 9 AND se.subeventID <= 13,CONCAT(' - ',categoryname,'
/>'),IF(se.subeventID = 1 OR se.subeventID = 2,'','
/>')),IF(se.subeventID = 4,CONCAT('Super
',st.subregtypename,''),IF(se.subeventID = 1 OR se.subeventID = 2 OR
se.subeventID =
20,'',CONCAT('',st.subregtypename,'')))),p.displayname FROM
(((((((participant2006_copy as p
LEFT OUTER JOIN captain2006_copy as c ON
p.participantID=c.relparticipantID)
LEFT OUTER JOIN team2006_copy as t ON t.relcaptainID=c.captainID)
LEFT OUTER JOIN registration2006_copy as r ON
p.participantID=r.relparticipantID)
LEFT OUTER JOIN regdsubevt2006_copy as rs ON
rs.relregistrationID=r.registrationID)
LEFT OUTER JOIN subevent2006_copy as se ON
rs.relsubeventID=se.subeventID)
LEFT OUTER JOIN category2006_copy as ct ON
se.subeventID=ct.relsubeventID)
LEFT OUTER JOIN subregtype as st ON se.relsubregtypeID=st.subregtypeID)
WHERE rs.relsubeventID IN ($subevtimplode)
ORDER BY p.lname,p.fname,t.teamname ASC

Re: bad query??

Can you post the table structure, the result of SHOW INDEX for the
tables involved and the EXPLAIN for this query?

Thank you!

Re: bad query??

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Re: bad query??

By using WHERE rs.relsubeventID IN ($subevtimplode), you are eliminating any
NULL rows of rs and therefore doing the same in r. This makes some of the
OUTER JOINs irrelevant; you're effectively doing an INNER JOIN from p->r and
r->rs. Or else you could put the condition inside the join condition for
rs, if an OUTER JOIN is truly what you need.

I suspect there may be other cases where you should use INNER JOIN instead
of OUTER JOIN in this query. Can you explain why you're using OUTER JOIN in
all these cases? The query might be doing more work than is necessary.

The EXPLAIN output suggests that it's using a temporary file to do a
filesort on rs. MySQL 5.0 improves filesorts greatly; you don't state which
version of MySQL you're using.

Do you have enough disk space for the filesort? If the disk is filling up
during the filesort, it could cause the server to churn and finally bring
down mysqld. Try the query again and monitor the disk space in your temp
filesystem, to see if it tops out.

The only indexes being used are during the joins between rs->se, and between
se->st. Have you considered creating indexes on your "rel" fields? That
is, the foreign key reference fields such as c.relparticipantID,
rs.relsubeventID, etc. Although MyISAM tables do not implement referential
integrity, it could benefit your join performance if you create indexes on
these fields.

Regarding whether you're doing too much in your select-list that you should
perhaps do in PHP application code instead, yes, I agree. I don't think it
is affecting your performance per se, but there's a lot of logic in the SQL
query that should be in your application layer. You even have hardcoded
HTML fragments, which shouldn't be in the SQL *or* the application layer --
they should be in a presentation layer, for instance if you use an HTML
template framework.

Finally, are you validating that your PHP variable $subevtimplode is
guaranteed to contain only a comma-separated list of integers? There is a
potential for a SQL injection security vulnerability unless you validate
this value.

Regards,
Bill K.

Re: bad query??

Thanks a million!!

Bill Karwin wrote:

> By using WHERE rs.relsubeventID IN ($subevtimplode), you are eliminating any
> NULL rows of rs and therefore doing the same in r. This makes some of the
> OUTER JOINs irrelevant; you're effectively doing an INNER JOIN from p->r and
> r->rs. Or else you could put the condition inside the join condition for
> rs, if an OUTER JOIN is truly what you need.
>
> I suspect there may be other cases where you should use INNER JOIN instead
> of OUTER JOIN in this query. Can you explain why you're using OUTER JOIN in
> all these cases? The query might be doing more work than is necessary.

Use of all OUTER is due to hast on my part and time constraints from the
client wanting things done yesterday. I am definitely going to take a
closer look at INNER and OUTER JOINs to improve the rest of my queries.

I changed the 2 you suggested to INNER and the almost 600 results coming
up in a flash. I decided to change others as well, and I am now only
using OUTER JOIN for just the first 2 joins.

>
> The EXPLAIN output suggests that it's using a temporary file to do a
> filesort on rs. MySQL 5.0 improves filesorts greatly; you don't state which
> version of MySQL you're using.

My host has version 4.0.25. They are due to upgrade soon.

>
> Do you have enough disk space for the filesort? If the disk is filling up
> during the filesort, it could cause the server to churn and finally bring
> down mysqld. Try the query again and monitor the disk space in your temp
> filesystem, to see if it tops out.

My host gives me 1.5GB of disk space, so with the correct JOINs used I
would probably have plenty.

>
> The only indexes being used are during the joins between rs->se, and between
> se->st. Have you considered creating indexes on your "rel" fields? That
> is, the foreign key reference fields such as c.relparticipantID,
> rs.relsubeventID, etc. Although MyISAM tables do not implement referential
> integrity, it could benefit your join performance if you create indexes on
> these fields.
>
> Regarding whether you're doing too much in your select-list that you should
> perhaps do in PHP application code instead, yes, I agree. I don't think it
> is affecting your performance per se, but there's a lot of logic in the SQL
> query that should be in your application layer. You even have hardcoded
> HTML fragments, which shouldn't be in the SQL *or* the application layer --
> they should be in a presentation layer, for instance if you use an HTML
> template framework.
>
> Finally, are you validating that your PHP variable $subevtimplode is
> guaranteed to contain only a comma-separated list of integers? There is a
> potential for a SQL injection security vulnerability unless you validate
> this value.

The value coming in is an event id which is then used to retrieve
subevent ids that are imploded and used with IN. It is also validated.
>
> Regards,
> Bill K.
>
>