Hi Chris,

Chris Hemmings wrote:
> Dan Buettner wrote:
>> Chris, a couple of thoughts -
>>
>> First, your index on the section is doing you no good (at this time)
>> since
>> all the values are the same. You may already know that, but thought I'd
>> mention it.
>>
>> Second, my theory on why query #1 is faster - if all your prices range
>> from
>> 1 up, and you're querying for prices greater than 0, then MySQL can just
>> return the first 30 rows after sorting them.
>>
>> The second query, where you are looking for prices greater than 1,
>> MySQL has
>> to sort and then examine a number of rows until it finds enough matching
>> rows (price > 1) to satisfy your query. This likely takes a little bit of
>> time. How many rows do you have with price = 1? It would have to
>> scan over
>> that many before it could start satisfying your query, if you think about
>> it.
>>
>> HTH,
>> Dan
>>
>>
>>
>> On 9/10/07, Chris Hemmings <lists@cheeky.org> 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 :-)
>>>
>>> Ta!
>>>
>>> Chris.
>>>
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>>>
>>>
>>
>
> Thanks Dan,
>
> I've got you on the section index... I was going to use that later, when
> I get somre real data in there.
>
> Anyway, I agree with your logic, but, the inverse is happening. The
> one where it has to actually exclude some rows (because price>1) is
> actually faster. Thats really why this has me baffled, I would presume
> that the price>1 would be slower as it does have to filter rows out first.

There's an easy way to find out: FLUSH STATUS, run the query, SHOW
STATUS LIKE 'handler%'. Do this on an otherwise quiet server if
possible.Or use MySQL Query Profiler -- it does a lot of math for you
:-)Baron