'\" t
.\" Title: \fBmysqlindexcheck\fR
.\" Author: [FIXME: author] [see http://docbook.sf.net/el/author]
.\" Generator: DocBook XSL Stylesheets v1.78.1
.\" Date: 08/30/2013
.\" Manual: MySQL Utilities
.\" Source: MySQL 1.3.4
.\" Language: English
.\"
.TH "\FBMYSQLINDEXCHECK\F" "1" "08/30/2013" "MySQL 1\&.3\&.4" "MySQL Utilities"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.\" mysqlindexcheck
.\" utilities: mysqlindexcheck
.\" scripts
.SH "NAME"
mysqlindexcheck \- Identify Potentially Redundant Table Indexes
.SH "SYNOPSIS"
.HP \w'\fBmysqlindexcheck\ [\fR\fB\fIoptions\fR\fR\fB]\ \fR\fB\fIdb\fR\fR\fB[\fR\fB\fI:table\fR\fR\fB]\ \&.\&.\&.\fR\ 'u
\fBmysqlindexcheck [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIdb\fR\fR\fB[\fR\fB\fI:table\fR\fR\fB] \&.\&.\&.\fR
.SH "DESCRIPTION"
.PP
This utility reads the indexes for one or more tables and identifies duplicate and potentially redundant indexes\&.
.PP
To check all tables in a database, specify only the database name\&. To check a specific table, name the table in
\fIdb\&.table\fR
format\&. It is possible to mix database and table names\&.
.PP
You can scan tables in any database except the internal databases
\fBmysql\fR,
\fBINFORMATION_SCHEMA\fR, and
\fBperformance_schema\fR\&.
.PP
Depending on the index type, the utility applies the following rules to compare indexes (designated as
idx_a
and
idx_b):
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBBTREE\fR
.sp
idx_b
is redundant to
idx_a
if and only if the first
\fIn\fR
columns in
idx_b
also appear in
idx_a\&. Order and uniqueness count\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBHASH\fR
.sp
idx_a
and
idx_b
are duplicates if and only if they contain the same columns in the same order\&. Uniqueness counts\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBSPATIAL\fR
.sp
idx_a
and
idx_b
are duplicates if and only if they contain the same column (only one column is permitted)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBFULLTEXT\fR
.sp
idx_b
is redundant to
idx_a
if and only if all columns in
idx_b
are included in
idx_a\&. Order counts\&.
.RE
.PP
To see
\fBDROP\fR
statements to drop redundant indexes, specify the
\fB\-\-show\-drops\fR
option\&. To examine the existing indexes, use the
\fB\-\-verbose\fR
option, which prints the equivalent
\fBCREATE INDEX\fR
(or
\fBALTER TABLE\fR
for primary keys\&.
.PP
To display the best or worst nonprimary key indexes for each table, use the
\fB\-\-best\fR
or
\fB\-\-worst\fR
option\&. This causes the output to show the best or worst indexes from tables with 10 or more rows\&. By default, each option shows five indexes\&. To override that, provide an integer value for the option\&.
.PP
To change the format of the index lists displayed for the
\fB\-\-show\-indexes\fR,
\fB\-\-best\fR, and
\fB\-\-worst\fR
options, use one of the following values with the
\fB\-\-format\fR
option:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBgrid\fR
(default)
.sp
Display output in grid or table format like that of the
\fBmysql\fR
monitor\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBcsv\fR
.sp
Display output in comma\-separated values format\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBtab\fR
.sp
Display output in tab\-separated format\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBsql\fR
.sp
print SQL statements rather than a list\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBvertical\fR
.sp
Display output in single\-column format like that of the
\eG
command for the
\fBmysql\fR
monitor\&.
.RE
.PP
Note: The
\fB\-\-best\fR
and
\fB\-\-worst\fR
lists cannot be printed as SQL statements\&.
OPTIONS
.PP
\fBmysqlindexcheck\fR
accepts the following command\-line options:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-help
.sp
Display a help message and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-best[=]
.sp
If
\fB\-\-stats\fR
is given, limit index statistics to the best
\fIN\fR
indexes\&. The default value of
\fIN\fR
is 5 if omitted\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-format=, \-f
.sp
Specify the index list display format for output produced by
\fB\-\-stats\fR\&. Permitted format values are
\fBgrid\fR,
\fBcsv\fR,
\fBtab\fR,
\fBsql\fR, and
\fBvertical\fR\&. The default is
\fBgrid\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-server=