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.

< Day Day Up >