Jeremy Cole wrote:
> Hi Chris,
>
> Chris Hemmings wrote:
>> Hello,
>>
>> I have a table, currently holding 128,978 rows... In this table, I
>> have a section column (int) and a price column (int). Every row has
>> a section of 1 currently, every row has a price, ranging from 1 to
>> 10,000.
>>
>> I have an index on both columns separately.
>>
>> Have a look at these two queries, can someone tell me why there is
>> such a difference in speed of execution? (Note difference in price
>> qualifier)
>>
>> ########################################
>>
>> SELECT *
>> FROM `table1`
>> WHERE price >0
>> AND section =1
>> ORDER BY price
>> LIMIT 0 , 30
>>
>> Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec)
>>
>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL
>> 96734 Using where; Using filesort
>>
>> ########################################
>>
>> SELECT *
>> FROM `table1`
>> WHERE price >1
>> AND section =1
>> ORDER BY price
>> LIMIT 0 , 30
>>
>>
>> Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec)
>>
>> Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL
>> 96734 Using where; Using filesort
>>
>> ########################################
>>
>> Other info:
>>
>> Query cacheing = off
>> MySQL version = 5.0.32
>> OS = Debian Sarge
>>
>> Sure, the second query returns 29 fewer records than the first, but
>> should that make the difference in time?
>>
>> Hope you can shed some light onto this :-)
>
> Did you run both queries multiple times and average the time taken?
> Otherwise, it seems likely that in one instance the data was cached,
> and in the other it was not. The query_cache being off does not
> affect caching in this sense.
>
> Regards,
>
> Jeremy
>

Jeremy, Thanks for the swift reply :-)

I have tried the query multiple times... Just to double check, here are
the timings for each, 5 times:

SELECT *
FROM `table1`
WHERE price >0
AND section =1
ORDER BY price
LIMIT 0 , 30

Showing rows 0 - 29 (128,978 total, Query took 0.9605 sec)
Showing rows 0 - 29 (128,978 total, Query took 0.9506 sec)
Showing rows 0 - 29 (128,978 total, Query took 0.9556 sec)
Showing rows 0 - 29 (128,978 total, Query took 0.9614 sec)
Showing rows 0 - 29 (128,978 total, Query took 0.9474 sec)

SELECT *
FROM `table1`
WHERE price >1
AND section =1
ORDER BY price
LIMIT 0 , 30

Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec)
Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec)
Showing rows 0 - 29 (128,949 total, Query took 0.0009 sec)
Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec)
Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec)

Wierd huh?

Would you like any of the mysqld runtime settings?

Chris.