mysqlindexcheck(1)

1MYSQLINDEXCHECK                 MySQL Utilities                MYSQLINDEXCHECK
2
3
4

NAME

6       mysqlindexcheck - Identify Potentially Redundant Table Indexes
7

SYNOPSIS

9       mysqlindexcheck [options] db[:table] ...
10

DESCRIPTION

12       This utility reads the indexes for one or more tables and identifies
13       duplicate and potentially redundant indexes.
14
15       To check all tables in a database, only specify the database name. To
16       check a specific table, name the table in db.table format. It is
17       possible to mix database and table names.
18
19       You can scan tables in any database except the internal databases
20       mysql, INFORMATION_SCHEMA, and performance_schema.
21
22       Depending on the index type, the utility applies the following rules to
23       compare indexes (designated as idx_a and idx_b):
24
25       ·   BTREE
26
27           idx_b is redundant to idx_a if and only if all the columns from
28           idx_b are a prefix of idx_a. Order and uniqueness count.
29
30       ·   HASH
31
32           idx_a and idx_b are redundant if they are duplicates, i.e. if and
33           only if they contain the same columns in the same order.
34
35       ·   SPATIAL
36
37           idx_a and idx_b are duplicates if and only if they contain the same
38           column (only one column is permitted).
39
40       ·   FULLTEXT
41
42           idx_b is redundant to idx_a if and only if all columns in idx_b are
43           included in idx_a. Order does not count.
44
45       To see DROP statements drop redundant indexes, specify the --show-drops
46       option. To examine the existing indexes, use the --verbose option,
47       which prints the equivalent CREATE INDEX (or ALTER TABLE) for primary
48       keys.
49
50       To display the best or worst non-primary key indexes for each table,
51       use the --best or --worst option. This causes the output to show the
52       best or worst indexes from tables with 10 or more rows. By default,
53       each option shows five indexes. To override that, provide an integer
54       value for the option.
55
56       To change the format of the index lists displayed for the
57       --show-indexes, --best, and --worst options, use one of the following
58       values with the --format option:
59
60       ·   grid (default)
61
62           Display output in grid or table format like that of the mysql
63           client command-line tool.
64
65       ·   csv
66
67           Display output in comma-separated values format.
68
69       ·   tab
70
71           Display output in tab-separated format.
72
73       ·   sql
74
75           Print SQL statements rather than a list.
76
77       ·   vertical
78
79           Display output in single-column format like that of the \G command
80           for the mysql client command-line tool.
81
82           Note
83           The --best and --worst lists cannot be printed as SQL statements.
84       OPTIONS.PP mysqlindexcheck accepts the following command-line options:
85
86       ·   --help
87
88           Display a help message and exit.
89
90       ·   --license
91
92           Display license information and exit.
93
94       ·   --best[=<N>]
95
96           If --stats is given, limit index statistics to the best N indexes.
97           The default value of N is 5 if omitted.
98
99       ·   --format=<index_format>, -f<index_format>
100
101           Specify the index list display format for output produced by
102           --stats. Permitted format values are grid, csv, tab, sql, and
103           vertical. The default is grid.
104
105       ·   --report-indexes, -r
106
107           Reports if a table has neither UNIQUE indexes nor a PRIMARY key.
108
109       ·   --server=<source>
110
111           Connection information for the server.
112
113           To connect to a server, it is necessary to specify connection
114           parameters such as user name, host name, password, and either a
115           port or socket. MySQL Utilities provides a number of ways to
116           provide this information. All of the methods require specifying
117           your choice via a command-line option such as --server, --master,
118           --slave, etc. The methods include the following in order of most
119           secure to least secure.
120
121           ·   Use login-paths from your .mylogin.cnf file (encrypted, not
122               visible). Example : <login-path>[:<port>][:<socket>]
123
124           ·   Use a configuration file (unencrypted, not visible) Note:
125               available in release-1.5.0. Example :
126               <configuration-file-path>[:<section>]
127
128           ·   Specify the data on the command-line (unencrypted, visible).
129               Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
130
131
132       ·   --show-drops, -d
133
134           Display DROP statements for dropping indexes.
135
136       ·   --show-indexes, -i
137
138           Display indexes for each table.
139
140       ·   --skip, -s
141
142           Skip tables that do not exist.
143
144       ·   --ssl-ca
145
146           The path to a file that contains a list of trusted SSL CAs.
147
148       ·   --ssl-cert
149
150           The name of the SSL certificate file to use for establishing a
151           secure connection.
152
153       ·   --ssl-cert
154
155           The name of the SSL key file to use for establishing a secure
156           connection.
157
158       ·   --ssl
159
160           Specifies if the server connection requires use of SSL. If an
161           encrypted connection cannot be established, the connection attempt
162           fails. Default setting is 0 (SSL not required).
163
164       ·   --stats
165
166           Show index performance statistics.
167
168       ·   --verbose, -v
169
170           Specify how much information to display. Use this option multiple
171           times to increase the amount of information. For example, -v =
172           verbose, -vv = more verbose, -vvv = debug.
173
174       ·   --version
175
176           Display version information and exit.
177
178       ·   --worst[=<N>]
179
180           If --stats is also passed in, limit index statistics to the worst N
181           indexes. The default value of N is 5, if omitted.
182       NOTES.PP You must provide connection parameters (user, host, password,
183       and so forth) for an account that has the appropriate privileges to
184       read all objects accessed during the operation.
185
186       For the --format option, the permitted values are not case sensitive.
187       In addition, values may be specified as any unambiguous prefix of a
188       valid value. For example, --format=g specifies the grid format. An
189       error occurs if a prefix matches more than one valid value.
190
191       The path to the MySQL client tools should be included in the PATH
192       environment variable in order to use the authentication mechanism with
193       login-paths. This will allow the utility to use the my_print_defaults
194       tools which is required to read the login-path values from the login
195       configuration file (.mylogin.cnf).  EXAMPLES.PP To check all tables in
196       the employees database on the local server to see the possible
197       redundant and duplicate indexes, use this command:
198
199           shell> mysqlindexcheck --server=root@localhost employees
200           # Source on localhost: ... connected.
201           # The following indexes are duplicates or redundant \
202             for table employees.dept_emp:
203           #
204           CREATE INDEX emp_no ON employees.dept_emp (emp_no) USING BTREE
205           #     may be redundant or duplicate of:
206           ALTER TABLE employees.dept_emp ADD PRIMARY KEY (emp_no, dept_no)
207           # The following indexes are duplicates or redundant \
208             for table employees.dept_manager:
209           #
210           CREATE INDEX emp_no ON employees.dept_manager (emp_no) USING BTREE
211           #     may be redundant or duplicate of:
212           ALTER TABLE employees.dept_manager ADD PRIMARY KEY (emp_no, dept_no)
213           # The following indexes are duplicates or redundant \
214             for table employees.salaries:
215           #
216           CREATE INDEX emp_no ON employees.salaries (emp_no) USING BTREE
217           #     may be redundant or duplicate of:
218           ALTER TABLE employees.salaries ADD PRIMARY KEY (emp_no, from_date)
219           # The following indexes are duplicates or redundant \
220             for table employees.titles:
221           #
222           CREATE INDEX emp_no ON employees.titles (emp_no) USING BTREE
223           #     may be redundant or duplicate of:
224           ALTER TABLE employees.titles ADD PRIMARY KEY (emp_no, title, from_date)
225
226       PERMISSIONS REQUIRED.PP Regarding the privileges needed to run this
227       utility, the user needs SELECT privilege on the mysql database as well
228       as for the databases which tables are being checked.
229

COPYRIGHT

231       Copyright © 2006, 2015, Oracle and/or its affiliates. All rights
232       reserved.
233
234       This documentation is free software; you can redistribute it and/or
235       modify it only under the terms of the GNU General Public License as
236       published by the Free Software Foundation; version 2 of the License.
237
238       This documentation is distributed in the hope that it will be useful,
239       but WITHOUT ANY WARRANTY; without even the implied warranty of
240       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
241       General Public License for more details.
242
243       You should have received a copy of the GNU General Public License along
244       with the program; if not, write to the Free Software Foundation, Inc.,
245       51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
246       http://www.gnu.org/licenses/.
247
248

AUTHOR

255       Oracle Corporation (http://dev.mysql.com/).
256
257
258
259MySQL 1.5.6                       09/15/2015                   MYSQLINDEXCHECK

NAME

SYNOPSIS

DESCRIPTION

COPYRIGHT

SEE ALSO

AUTHOR