-----Forwarded by Peter J Milanese/MHT/Nypl on 01/20/2004 02:37PM -----
To: <[EMAIL PROTECTED]>
From: Peter J Milanese/MHT/Nypl
Date: 01/20/2004 02:34PM
cc: <[EMAIL PROTECTED]>
Subject: RE: Slow query times
You may also want to try :
count(1)
instead of
count(*)
count(*) pulls back the data while count(1) does not.
Peter J. Milanese
-----"Matt Griffin" <[EMAIL PROTECTED]> wrote: -----
To: <[EMAIL PROTECTED]>
From: "Matt Griffin" <[EMAIL PROTECTED]>
Date: 01/20/2004 02:33PM
Subject: RE: Slow query times
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]
