Re: Why are my sorts so slow?

[Brent Baisley]

Sorts don't use indexes, for the most part, only the search part does. 
Someone correct me if I'm wrong on that, I think I recall reading it in 
SQL for Smarties. The index is on the entire database, not on the 
subset that you have selected. If you know that your query is going to 
return most of the database, you may want to use HAVING instead of 
WHERE. Then the index can be used on the sort and the filter will be 
applied after the sort. Keep in mind that the index won't be used on 
the filter then.

On Friday, June 13, 2003, at 02:08 PM, Bruce Leidl wrote:

I'm having a problem with some very slow queries that spend a very 
long time in the 'Sorting result' state and I'm wondering how sorts 
are implemented in mysql and what I can do to optimize these types of 
queries.

The query looks something like this:

SELECT col1,col2,col3 from table1 where col1 = 0 ORDER BY col2 DESC 
LIMIT 10;

Both col1 and col2 have individual indexes (idx_1, idx_2)

Running an EXPLAIN on the query gives me the following information:

table:          table1
type:           ref
possible_keys:  idx_1
key:            idx_1
key_len:        4
ref:            const
rows:           10269
extra:          where used; Using filesort
I understand that this is retrieving a lot of rows which is slow, but 
after the rows have been retrieved shouldn't the sort execute very 
quickly using the index on the sorted column?  I notice that the 
EXPLAIN makes no mention at all of the index on the column being 
sorted.  What is a filesort and what are the different ways that mysql 
can sort a result set?

thanks,

--brl







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]