"Jay Blanchard" <jay.blanchard@stripped> wrote on 08/01/2005
> 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
> 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;
> Proper indexing will solve your problem. Index bdate and level, that
> should speed things up considerably.
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.
Unimin Corporation - Spruce Pine