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]
