List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:January 21 2004 8:38pm
Subject:Re: Slow query times
View as plain text  
Chuck Gadd said:
> Balazs Rauznitz wrote:
>
>> 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 ?
>
> Well, MySql can only use 1 index per table to optimize a query.
>
> It's apparently using the index on ID, so it then needs to examine
> all records in the right ID range to see if they meet the
> sex='M' condition.
>
> You could build an index on both fields as one index, and MySql
> should be able to use it to resolve both parts of the query.
>
> create index id_and_sex_index on sex (id,sex);

Does that really matter? We are talking about 5 byte rows (+
overhead). What is the minimum size you get back from a disk read? 512
bytes? 8192 bytes? How many records will there be in 1 disk read? What
is the chance that all records are M or F and thus the read would not
be necessary?

RAM might be cheap nowadays, but wouldn't you loose more by crowding
out the key bufer as you gain by reducing in-memory comparisons (I
seriously doubt it will save you any disk I/O)?

Jochem




Thread
Slow query timesBalazs Rauznitz20 Jan
  • RE: Slow query timesMatt Griffin20 Jan
    • Re: Slow query timesBalazs Rauznitz20 Jan
      • RE: Slow query timesMatt Griffin20 Jan
        • RE: Slow query timesMatt Griffin20 Jan
  • Re: Slow query timesmos20 Jan
  • Re: Slow query timesChuck Gadd21 Jan
    • Re: Slow query timesJochem van Dieten21 Jan
RE: Slow query timesPeter J Milanese20 Jan
  • Re: Slow query timesMatt W21 Jan