1MYSQLINDEXCHECK(1) MySQL Utilities MYSQLINDEXCHECK(1)
2
6 mysqlindexcheck - Check for redundant or duplicate indexes on a list
7 tables or databases
8
10 mysqlcheckindex --server=<user>[<passwd>]@<host>:[<port>][:<socket>]
11 [[ --help | --version ] |
12 [ --show-drops | --skip | --verbose | --show-indexes |
13 --quiet | --index-format=[GRID|SQL|TAB|CSV] |
14 --stats [--best=<num_rows> | --worst=<num rows> ]]
15 <db> | [ ,<db> | ,<db.table> | , <db.table>]]
16
18 This utility is used to read the indexes for one or more tables and
19 identify duplicate and potentially redundant indexes. The following
20 rules are applied during the operation.
21 BTREE
23 idx_b is redundant to idx_a iff the first n columns in idx_b
25 also appear in idx_a. Order and uniqueness count.
26 HASH
28 idx_a and idx_b are duplicates iff they contain the same columns
30 in the same order and uniqueness counts.
31 SPATIAL
33 idx_a and idx_b are duplicates iff they contain the same column
35 (only 1 column is permitted)
36 FULLTEXT
38 idx_b is redundant to idx_a iff all columns in idx_b are
40 included in idx_a (order is not important)
41 You can specify scanning all of the tables for any database (except the
43 internal databases mysql, INFORMATION_SCHEMA, PERFORMANCE_SCHEMA) by
44 specifying only the database name or you can specify a list of tables
45 (in the form db.tablename) which will limit the scan to only those
46 tables in the databases listed and those tables listed.
47 If you want to see the example DROP statements to drop the redundant
49 indexes, you can specify the --show-drops option (see below). You can
50 also examine the existing indexes using the --verbose option which
51 prints the equivalent CREATE INDEX (or ALTER TABLE for primary keys).
52 You can also display the best and worst non-primary key indexes for
54 each table with the --best and --worst options. The data will show the
55 top 5 indexes from tables with 10 or more rows.
56 You can change the display format of the index lists for
58 --show-indexes, --best, and --worst in one of the following formats:
59 TABLE (default)
61 print a mysql-like table output
63 TAB
65 print using tabs for separation
67 CSV
69 print using commas for separation
71 SQL
73 print SQL statements rather than a list.
75 Note: the --best and --worst lists cannot be printed as SQL statements.
77 You must provide login information such as user, host, password, etc.
79 for a user that has the appropriate rights to access all objects in the
80 operation. See mysqlindexcheck-notes below for more details.
81
83 --version
84 show version number and exit
85 --help show the help page
87 --server <source>
89 connection information for source server in the form:
91 <user>:<password>@<host>:<port>:<socket>
92 --show-drops, -d
94 display DROP statements for dropping indexes
96 --show-indexes, -i
98 display indexes for each table
100 --skip, -s
102 skip tables that do not exist
104 --verbose, -v
106 control how much information is displayed. For example, -v =
108 verbose, -vv = more verbose, -vvv = debug
109 --format <index-format>
111 Display the list of indexes per table in either SQL, TABLE
113 (default), TAB, CSV, or VERTICAL format
114 --stats
116 show index performance statistics
117 --best <num>
119 limit index statistics to the best N indexes
121 --worst <num>
123 limit index statistics to the worst N indexes
125
127 The login user must have the appropriate permissions to read all data‐
128 bases and tables listed.
129
131 To scan all of the tables in the employees database to see the possible
132 redundant and duplicate indexes as well as the DROP statements for the
133 indexes, use this command:
134 $ mysqlindexcheck --server=root@localhost employees
136 # Source on localhost: ... connected.
137 # The following indexes are duplicates or redundant \\
138 for table employees.dept_emp:
139 #
140 CREATE INDEX emp_no ON employees.dept_emp (emp_no) USING BTREE
141 # may be redundant or duplicate of:
142 ALTER TABLE employees.dept_emp ADD PRIMARY KEY (emp_no, dept_no)
143 # The following indexes are duplicates or redundant \\
144 for table employees.dept_manager:
145 #
146 CREATE INDEX emp_no ON employees.dept_manager (emp_no) USING BTREE
147 # may be redundant or duplicate of:
148 ALTER TABLE employees.dept_manager ADD PRIMARY KEY (emp_no, dept_no)
149 # The following indexes are duplicates or redundant \\
150 for table employees.salaries:
151 #
152 CREATE INDEX emp_no ON employees.salaries (emp_no) USING BTREE
153 # may be redundant or duplicate of:
154 ALTER TABLE employees.salaries ADD PRIMARY KEY (emp_no, from_date)
155 # The following indexes are duplicates or redundant \\
156 for table employees.titles:
157 #
158 CREATE INDEX emp_no ON employees.titles (emp_no) USING BTREE
159 # may be redundant or duplicate of:
160 ALTER TABLE employees.titles ADD PRIMARY KEY (emp_no, title, from_date)
161
163 Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
164 This program is free software; you can redistribute it and/or modify it
166 under the terms of the GNU General Public License as published by the
167 Free Software Foundation; version 2 of the License.
168 This program is distributed in the hope that it will be useful, but
170 WITHOUT ANY WARRANTY; without even the implied warranty of MER‐
171 CHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
172 Public License for more details.
173 You should have received a copy of the GNU General Public License along
175 with this program; if not, write to the Free Software Foundation, Inc.,
176 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
177
179 MySQL Utilities Team
180
182 2010, Oracle and/or its affiliates. All rights reserved.
1831.0.1 September 23, 2011 MYSQLINDEXCHECK(1)