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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > >
