[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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
