Databases Reference
In-Depth Information
mysql>
EXPLAIN SELECT title, release_year FROM sakila.film ORDER BY title\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 953
Extra:
However, the server can use the index for sorting only if it's sorted by the same collation
as the one the query specifies. The index is sorted by the column's collation, which in
this case is
utf8_general_ci
. If you want the results ordered by another collation, the
server will have to do a filesort:
mysql>
EXPLAIN SELECT title, release_year
->
FROM sakila.film ORDER BY title COLLATE utf8_bin\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 953
Extra:
Using filesort
In addition to accommodating your connection's default character set and any prefer-
ences you specify explicitly in queries, MySQL has to convert character sets so that it
can compare them when they're not the same. For example, if you join two tables on
character columns that don't have the same character set, MySQL has to convert one
of them. This conversion can make it impossible to use an index, because it is just like
a function enclosing the column. If you're not sure whether something like this is hap-
pening, you can use
EXPLAIN EXTENDED
followed by
SHOW WARNINGS
to look at the query
from the server's point of view. You'll see character sets in the query and you can often
tell if something is being translated between character sets.
The UTF-8 multibyte character set stores each character in a varying number of bytes
(between one and three). MySQL uses fixed-size buffers internally for many string op-
erations, so it must allocate enough space to accommodate the maximum possible
length. For example, a
CHAR(10)
encoded with UTF-8 requires 30 bytes to store, even
if the actual string contains no so-called “wide” characters. Variable-length fields
(
VARCHAR
,
TEXT
) do not suffer from this on disk, but in-memory temporary tables used
for processing and sorting queries will always allocate the maximum length needed.
