MySQL can use indexes to sort, but not when your WHERE clause contains a column other than the one being sorted on.
I think the MySQL manual has a good section on what 'filesort' means and when MySQL uses it. Basically it's selecting all the rows that meet your WHERE clause, then running a quicksort algorithm over the results. One thing you can do to speed up this kind of query is reducing the number of rows it has to sort before it can select the last 10, by using a more restrictive WHERE. For example, I had a query very much like this that displayed the last 50 records to be put in the database sorted by time. Since I knew more than 50 records a day came in, I added a 'WHERE Time >' clause to only select records from the last 24 hours. This made a huge difference because it was only selecting a couple hundred records instead of a million or so before doing the sort and throwing away all but the last 50. I don't know if that was clear or not, but what I'm trying to say is, if you can limit the amount of data that matches the WHERE that you're going to be throwing out anyway, do it. :) > -----Original Message----- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > 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. > 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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
