Re: FN-FORUM: MySQL query/table optimisation

date posted 23rd January 2007 15:55



Robin Vickery wrote:
>
> On 23 Jan 2007 13:15:14 -0000, Ben Moxon [EMAIL REMOVED] wrote:
>>
>> I've got a mysql query that is taking 23 seconds to run the first
>> time I call it, and about seven seconds subsequently. This appears to
>> be the case regardless of whether I include an SQL_NO_CACHE hint in
>> the query itself.
>
>
>
>>
>> The query is finding the range to which a number belongs to, between
>> two unsigned ints so currently I'm using "SELECT * FROM ranges WHERE
>> ( start the lower end values but take a long time for the higher values, less
>> long on subsequent queries to the same table. The table contains a
>> little over 4000000 rows.
>
> That's far too long. Even on my rather overloaded desktop, with 4
> million rows, that query takes less than 2.5 seconds the first time
> and returns from the cache in zero seconds subsequently.
>
It is indeed. Had I run that query I would have realised it, but I had
discarded from the end of that the "LIMIT 1" thinking it made no
difference. Oh boy was I wrong.

Limiting it to one result makes the query run at least 17 times slower...

-ben