Chuck Gadd said:
> Balazs Rauznitz wrote:
>
>> mysql> select count(*) from sex where id>459000 and id <=46
>> and sex = 'M'; +--+
>> | count(*) |
>> +--+
>> | 504 |
>> +--+
>> 1 row in set (5.09 sec)
>>
>> Any way to make this faster ?
>
> Well, MySql can only use 1
Balazs Rauznitz wrote:
However when the 'sex' column is involved:
mysql> select count(*) from sex where id>459000 and id <=46 and sex = 'M';
+--+
| count(*) |
+--+
| 504 |
+--+
1 row in set (5.09 sec)
Any way to make this faster ?
Well, MySql can only use 1 index
- Original Message -
From: "Peter J Milanese"
Sent: Tuesday, January 20, 2004 1:37 PM
Subject: RE: Slow query times
>
> You may also want to try :
>
> count(1)
>
> instead of
>
> count(*)
>
>
> count(*) pulls back the data while coun
;
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
At 11:52 AM 1/19/2004, you wrote:
While doing some benchmarks the other day, I saw surprisingly slow
query results on columns that were indexed.
It's because MySQL won't use the Sex_Index index because the value that you
are searching for (Namely "F" or "M") accounts for more than 30% of the
dat
PM
To: 'Balazs Rauznitz'
Cc: [EMAIL PROTECTED]
Subject: RE: Slow query times
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
ffin
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
auznitz [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'
-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(*) pu
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
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
ps: 'optimize table' seems to have no effect. I was also able to
reproduce this on a different mysql server...
Balazs
On Mon, Jan 19, 2004 at 10:39:26AM -0500, Balazs Rauznitz wrote:
>
> While doing some benchmarks the other day, I saw surprisingly slow
> query results on columns that were in
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
13 matches
Mail list logo
