List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 1 2005 1:49pm
Subject:RE: query unexpectedly took too long.
View as plain text  
"Jay Blanchard" <jay.blanchard@stripped> wrote on 08/01/2005 
09:20:59 AM:

> [snip]
> I have this query which gets executed often, but when I saw this today I
> 
> panicked. Any suggestions to why this took so long?
> 
> mysql> SELECT t2.id, t2.bdate, t2.level FROM bvolset AS t1 JOIN bvolset
> AS 
> t2 ON t2.bdate<t1.bdate AND t2.level<t1.level WHERE t1.id=30 ORDER BY 
> bdate DESC LIMIT 1;
> 
> Empty set (22.82 sec)
> 
> here is the schema and data:
> 
> CREATE TABLE bvolset (
>     id int(11) NOT NULL auto_increment,
>     bdate datetime default NULL,
>     level int(11) NOT NULL default '0',
>     PRIMARY KEY  (id),
>     UNIQUE KEY bdate (bdate),
>     KEY level (level)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> [/snip]
> 
> Proper indexing will solve your problem. Index bdate and level, that
> should speed things up considerably.
> 

Jay,

From the looks of things, bdate and level are *already* indexed. Or, did 
you want him to create a multi-column index on bdate and level? You 
weren't very clear in your suggestion.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
query unexpectedly took too long.jpyeron1 Aug
  • Re: query unexpectedly took too long.SGreen1 Aug
RE: query unexpectedly took too long.Jay Blanchard1 Aug
  • RE: query unexpectedly took too long.SGreen1 Aug
  • RE: query unexpectedly took too long.Jason Pyeron1 Aug
RE: query unexpectedly took too long.Jay Blanchard1 Aug