4.4 Index Maintenance
Once
you're done adding and dropping indexes, and your
application is running happily, you may wonder about any ongoing
index maintenance and administrative tasks. The good news is that
there's no requirement that you do anything special,
but there are a couple of things you may want to do from time to
time.
4.4.1 Obtaining Index Information
If you're ever
asked to help debug a slow query or indexing problem against a table
(or group of tables) that you haven't seen in quite
a while, you'll need to recover some basic
information. Which columns are indexed? How many values are there?
How large is the index?
Luckily, MySQL makes it relatively easy to gather this information.
By using SHOW CREATE
TABLE, you can retrieve the complete SQL necessary
to (re-)create the table. However, if you care only about indexes,
SHOW INDEXES
FROM provides a lot more information.
mysql> SHOW INDEXES FROM access_jeremy_zawodny_com \G
*************************** 1. row ***************************
Table: access_jeremy_zawodny_com
Non_unique: 1
Key_name: time_stamp
Seq_in_index: 1
Column_name: time_stamp
Collation: A
Cardinality: 9434851
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
1 rows in set (0.00 sec)
You may substitute KEYS for
INDEXES in the query.
The table in the example has a single index named
time_stamp. It is a B-tree index with only one
component, the time_stamp column (as opposed to a
multicolumn index). The index isn't packed and is
allowed to contain NULL values. It's a non-unique
index, so duplicates are allowed.
4.4.2 Refreshing Index Statistics
Over time, a table that sees many
changes is likely to develop some inefficiencies in its indexes.
Fragmentation due to blocks moving around on disk and inaccurate
index statistics are the two most common problems
you're likely to see. Luckily, it's
easy for MySQL to optimize index data for MyISAM tables.
You can use the
OPTIMIZE
TABLE command to reindex a table. In doing so,
MySQL will reread all the records in the table and reconstruct all of
its indexes. The result will be tightly packed indexes with good
statistics available.
Keep in mind that reindexing the table can take quite a bit of time
if the table is large. During that time, MySQL has a write lock on
the table, so data can't be updated.
Using the myisamchk command-line tool, you can
perform the analysis offline:
$ cd database-name
$ myisamchk table-name
Just be sure that MySQL isn't running when you try
this, or you run the risk of corrupting your indexes.
BDB and InnoDB tables are less
likely to need this sort of tuning. That's a good
thing, because the only ways to reindex them are a bit more time
consuming. You can manually drop and re-create all the indexes, or
you have to dump and reload the tables. However, using
ANALYZE TABLE on an InnoDB
table causes InnoDB to re-sample the data in an attempt to collect
better statistics.
|