I couldn't say without some hard benchmarks. Just keep in mind that an index still only breaks your data up into fairly large "compartments" which must be scanned. It's like this: rows scanned = # rows / (log2(# of options)) At some threshold of log2(# of options) it's faster to scan the whole table. MySQL usually detects this and ignores the index, especially if another index was already used to break down the number of rows.
Matt -----Original Message----- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 2:46 PM To: Matt Griffin Cc: [EMAIL PROTECTED] Subject: Re: Slow query times You are perfectly correct. Without the index on sex query times are lightning fast. I don't quite get you explanation why; where can I read up on how queries are done with and without an index ? Can one make a generalization that unless the number of different values in a column is less than log(number of rows), it does not make sense to use an index ? Thanks, Balazs On Tue, Jan 20, 2004 at 02:33:28PM -0500, Matt Griffin wrote: > 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] -- 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]
