For example, if you have a CHAR(20) column, but index only the first 10 bytes, the index cannot distinguish values past the 10th byte and a filesort will be needed. In this case, the index cannot be used to fully resolve the sort order. You index only a prefix of a column named in the ORDER BY clause.You have different ORDER BY and GROUP BY expressions.(This is the first table in the EXPLAIN output that does not have a const join type.) You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows.You use ORDER BY with an expression that includes terms other than the key column name:.SELECT * FROM t1 WHERE key2= constant ORDER BY key1 The key used to fetch the rows is not the same as the one used in the ORDER BY:.SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC SELECT * FROM t1 WHERE key2= constant ORDER BY key_part2 You use ORDER BY on nonconsecutive parts of a key:.In some cases, MariaDB cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. The following queries use the index to resolve the ORDER BY part: The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause. In some cases, MariaDB can use an index to satisfy an ORDER BY clause without doing any extra sorting.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |