PHWinfo - Afficher un message

10/09/2007, 20h11

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.

Still confused.....

Chris.

10/09/2007, 20h11	#5
Chris Hemmings Messages: n/a Hébergeur:	Re: Really strange index/speed issues 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. Still confused..... Chris.