Hi Balazs, The likely answer is the one that nobody mentioned: it's an optimizer bug in 4.0.16. If you look at the EXPLAIN output for the second query, it's probably using a ref type on the sex column, instead of the more restrictive id index. If so, that's the bug. From http://www.mysql.com/doc/en/News-4.0.17.html
"Fixed optimizer bug, introduced in 4.0.16, when REF access plan was preferred to more efficient RANGE on another column." If that's the problem, upgrading will fix it. :-) Matt ----- Original Message ----- From: "Balazs Rauznitz" Sent: Monday, January 19, 2004 9:39 AM Subject: Slow query times > > While doing some benchmarks the other day, I saw surprisingly slow > query results on columns that were indexed. > > Here's the table definition: > > create table sex ( > id integer, > sex char(1)); > create index id_index on sex (id); > create index sex_index on sex (sex); > > Then I loaded a million rows, id was from 1 to 1_000_000, sex was > randomly 'F' or 'M'. > > When searching on 'id' everything is snappy: > > mysql> select count(*) from sex where id>459000 and id <=460000; > +----------+ > | count(*) | > +----------+ > | 1000 | > +----------+ > 1 row in set (0.00 sec) > > However when the 'sex' column is involved: > > mysql> select count(*) from sex where id>459000 and id <=460000 and sex = 'M'; > +----------+ > | count(*) | > +----------+ > | 504 | > +----------+ > 1 row in set (5.09 sec) > > Any way to make this faster ? > > I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. > > <Insert jokes about sex making MySQL slow here> ;-) > > Thanks, > > Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
