470,849 Members | 1,358 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,849 developers. It's quick & easy.

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','Individual','Team'),IF(se.subeventID
= 9 AND se.subeventID <= 13,CONCAT(' - ',categoryname,'<BR

/>'),IF(se.subeventID = 1 OR se.subeventID = 2,'','<BR
/>')),IF(se.subeventID = 4,CONCAT('<B>Super
',st.subregtypename,'</B>'),IF(se.subeventID = 1 OR se.subeventID = 2 OR
se.subeventID =
20,'',CONCAT('<B>',st.subregtypename,'</B>')))),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

Feb 4 '06 #1
4 1556
Can you post the table structure, the result of SHOW INDEX for the
tables involved and the EXPLAIN for this query?

Thank you!

Feb 5 '06 #2

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

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


Feb 6 '06 #3
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.
Feb 6 '06 #4
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.


Feb 7 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by jaysonsch | last post: by
9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Harvey | last post: by
4 posts views Thread by Diamondback | last post: by
14 posts views Thread by Dave Thomas | last post: by
4 posts views Thread by Stan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.