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
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/do
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
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
-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
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
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
11 matches
Mail list logo
