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
--
high performance mysql consulting
www.provenscaling.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
