I wouldn't imagine that creating an index on a column with only two possible values could make things any faster. You only get a maximum 50% reduction in row scans, rather than the normal log based reduction with a random value distribution. In addition, you contend with the overhead of using the index. What is the runtime without the index?
Matt -----Original Message----- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 12:53 PM To: [EMAIL PROTECTED] 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
