"Jay Blanchard" <[EMAIL PROTECTED]> 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
