Re: Why are my sorts so slow?
Bruce, Bruce Leidl schrieb: 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) try a concatenated index col1_col2 the first part of the index can be used for the WHERE part, the second (hopefully) for the SORT part of your query. Regards, Frank. 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] -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why are my sorts so slow?
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]
Re: Why are my sorts so slow?
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]
RE: Why are my sorts so slow?
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]
