Databases Reference
In-Depth Information
... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_
id;
• Here's an example where MySQL could theoretically use an index to order a join,
but doesn't because the optimizer places the
film_actor
table second in the join
(the next chapter shows ways to change the join order):
mysql>
EXPLAIN SELECT actor_id, title FROM sakila.film_actor
->
INNER JOIN sakila.film USING(film_id) ORDER BY actor_id\G
+------------+----------------------------------------------+
| table | Extra |
+------------+----------------------------------------------+
| film | Using index; Using temporary; Using filesort |
| film_actor | Using index |
+------------+----------------------------------------------+
One of the most important uses for ordering by an index is a query that has both an
ORDER BY
and a
LIMIT
clause. We explore this in more detail later.
Packed (Prefix-Compressed) Indexes
MyISAM uses prefix compression to reduce index size, allowing more of the index to
fit in memory and dramatically improving performance in some cases. It packs string
values by default, but you can even tell it to compress integer values.
MyISAM packs each index block by storing the block's first value fully, then storing
each additional value in the block by recording the number of bytes that have the same
prefix, plus the actual data of the suffix that differs. For example, if the first value is
“perform” and the second is “performance,” the second value will be stored analo-
gously to “7,ance”. MyISAM can also prefix-compress adjacent row pointers.
Compressed blocks use less space, but they make some operations slower. Because
each value's compression prefix depends on the value before it, MyISAM can't do bi-
nary searches to find a desired item in the block and must scan the block from the
beginning. Sequential forward scans perform well, but reverse scans—such as
ORDER
BY DESC
—don't work as well. Any operation that requires finding a single row in the
middle of the block will require scanning, on average, half the block.
Our benchmarks have shown that packed keys make index lookups on MyISAM tables
perform several times more slowly for a CPU-bound workload, because of the scans
required for random lookups. Reverse scans of packed keys are even slower. The trade-
off is one of CPU and memory resources versus disk resources. Packed indexes can be
about one-tenth the size on disk, and if you have an I/O-bound workload they can more
than offset the cost for some queries.
You can control how a table's indexes are packed with the
PACK_KEYS
option to
CREATE
TABLE
.
