From: Chris Hemmings (listscheeky.org)
Date: Mon Sep 10 2007 - 13:19:34 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]