1MK-DUPLICATE-KEY-CHECKEURs(e1r)Contributed Perl DocumentMaKt-iDoUnPLICATE-KEY-CHECKER(1)
2
6 mk-duplicate-key-checker - Find duplicate indexes and foreign keys on
7 MySQL tables.
8
10 Usage: mk-duplicate-key-checker [OPTION...] [DSN]
11 mk-duplicate-key-checker examines MySQL tables for duplicate or
13 redundant indexes and foreign keys. Connection options are read from
14 MySQL option files.
15 mk-duplicate-key-checker --host host1
17
19 The following section is included to inform users about the potential
20 risks, whether known or unknown, of using this tool. The two main
21 categories of risks are those created by the nature of the tool (e.g.
22 read-only tools vs. read-write tools) and those created by bugs.
23 mk-duplicate-key-checker is a read-only tool that executes SHOW CREATE
25 TABLE and related queries to inspect table structures, and thus is very
26 low-risk.
27 At the time of this release, there is an unconfirmed bug that causes
29 the tool to crash.
30 The authoritative source for updated information is always the online
32 issue tracking system. Issues that affect this tool will be marked as
33 such. You can see a list of such issues at the following URL:
34 <http://www.maatkit.org/bugs/mk-duplicate-key-checker>.
35 See also "BUGS" for more information on filing bugs and getting help.
37
39 This program examines the output of SHOW CREATE TABLE on MySQL tables,
40 and if it finds indexes that cover the same columns as another index in
41 the same order, or cover an exact leftmost prefix of another index, it
42 prints out the suspicious indexes. By default, indexes must be of the
43 same type, so a BTREE index is not a duplicate of a FULLTEXT index,
44 even if they have the same columns. You can override this.
45 It also looks for duplicate foreign keys. A duplicate foreign key
47 covers the same columns as another in the same table, and references
48 the same parent table.
49
51 This tool accepts additional command-line arguments. Refer to the
52 "SYNOPSIS" and usage information for details.
53 --all-structs
55 Compare indexes with different structs (BTREE, HASH, etc).
56 By default this is disabled, because a BTREE index that covers the
58 same columns as a FULLTEXT index is not really a duplicate, for
59 example.
60 --ask-pass
62 Prompt for a password when connecting to MySQL.
63 --charset
65 short form: -A; type: string
66 Default character set. If the value is utf8, sets Perl's binmode
68 on STDOUT to utf8, passes the mysql_enable_utf8 option to
69 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
70 other value sets binmode on STDOUT without the utf8 layer, and runs
71 SET NAMES after connecting to MySQL.
72 --[no]clustered
74 default: yes
75 PK columns appended to secondary key is duplicate.
77 Detects when a suffix of a secondary key is a leftmost prefix of
79 the primary key, and treats it as a duplicate key. Only detects
80 this condition on storage engines whose primary keys are clustered
81 (currently InnoDB and solidDB).
82 Clustered storage engines append the primary key columns to the
84 leaf nodes of all secondary keys anyway, so you might consider it
85 redundant to have them appear in the internal nodes as well. Of
86 course, you may also want them in the internal nodes, because just
87 having them at the leaf nodes won't help for some queries. It does
88 help for covering index queries, however.
89 Here's an example of a key that is considered redundant with this
91 option:
92 PRIMARY KEY (`a`)
94 KEY `b` (`b`,`a`)
95 The use of such indexes is rather subtle. For example, suppose you
97 have the following query:
98 SELECT ... WHERE b=1 ORDER BY a;
100 This query will do a filesort if we remove the index on "b,a". But
102 if we shorten the index on "b,a" to just "b" and also remove the
103 ORDER BY, the query should return the same results.
104 The tool suggests shortening duplicate clustered keys by dropping
106 the key and re-adding it without the primary key prefix. The
107 shortened clustered key may still duplicate another key, but the
108 tool cannot currently detect when this happens without being ran a
109 second time to re-check the newly shortened clustered keys.
110 Therefore, if you shorten any duplicate clustered keys, you should
111 run the tool again.
112 --config
114 type: Array
115 Read this comma-separated list of config files; if specified, this
117 must be the first option on the command line.
118 --databases
120 short form: -d; type: hash
121 Check only this comma-separated list of databases.
123 --defaults-file
125 short form: -F; type: string
126 Only read mysql options from the given file. You must give an
128 absolute pathname.
129 --engines
131 short form: -e; type: hash
132 Check only tables whose storage engine is in this comma-separated
134 list.
135 --help
137 Show help and exit.
138 --host
140 short form: -h; type: string
141 Connect to host.
143 --ignore-databases
145 type: Hash
146 Ignore this comma-separated list of databases.
148 --ignore-engines
150 type: Hash
151 Ignore this comma-separated list of storage engines.
153 --ignore-order
155 Ignore index order so KEY(a,b) duplicates KEY(b,a).
156 --ignore-tables
158 type: Hash
159 Ignore this comma-separated list of tables. Table names may be
161 qualified with the database name.
162 --key-types
164 type: string; default: fk
165 Check for duplicate f=foreign keys, k=keys or fk=both.
167 --password
169 short form: -p; type: string
170 Password to use when connecting.
172 --pid
174 type: string
175 Create the given PID file. The file contains the process ID of the
177 script. The PID file is removed when the script exits. Before
178 starting, the script checks if the PID file already exists. If it
179 does not, then the script creates and writes its own PID to it. If
180 it does, then the script checks the following: if the file contains
181 a PID and a process is running with that PID, then the script dies;
182 or, if there is no process running with that PID, then the script
183 overwrites the file with its own PID and starts; else, if the file
184 contains no PID, then the script dies.
185 --port
187 short form: -P; type: int
188 Port number to use for connection.
190 --set-vars
192 type: string; default: wait_timeout=10000
193 Set these MySQL variables. Immediately after connecting to MySQL,
195 this string will be appended to SET and executed.
196 --socket
198 short form: -S; type: string
199 Socket file to use for connection.
201 --[no]sql
203 default: yes
204 Print DROP KEY statement for each duplicate key. By default an
206 ALTER TABLE DROP KEY statement is printed below each duplicate key
207 so that, if you want to remove the duplicate key, you can copy-
208 paste the statement into MySQL.
209 To disable printing these statements, specify --nosql.
211 --[no]summary
213 default: yes
214 Print summary of indexes at end of output.
216 --tables
218 short form: -t; type: hash
219 Check only this comma-separated list of tables.
221 Table names may be qualified with the database name.
223 --user
225 short form: -u; type: string
226 User for login if not current user.
228 --verbose
230 short form: -v
231 Output all keys and/or foreign keys found, not just redundant ones.
233 --version
235 Show version and exit.
236
238 These DSN options are used to create a DSN. Each option is given like
239 "option=value". The options are case-sensitive, so P and p are not the
240 same option. There cannot be whitespace before or after the "=" and if
241 the value contains whitespace it must be quoted. DSN options are
242 comma-separated. See the maatkit manpage for full details.
243 · A
245 dsn: charset; copy: yes
247 Default character set.
249 · D
251 dsn: database; copy: yes
253 Default database.
255 · F
257 dsn: mysql_read_default_file; copy: yes
259 Only read default options from the given file
261 · h
263 dsn: host; copy: yes
265 Connect to host.
267 · p
269 dsn: password; copy: yes
271 Password to use when connecting.
273 · P
275 dsn: port; copy: yes
277 Port number to use for connection.
279 · S
281 dsn: mysql_socket; copy: yes
283 Socket file to use for connection.
285 · u
287 dsn: user; copy: yes
289 User for login if not current user.
291
293 You can download Maatkit from Google Code at
294 <http://code.google.com/p/maatkit/>, or you can get any of the tools
295 easily with a command like the following:
296 wget http://www.maatkit.org/get/toolname
298 or
299 wget http://www.maatkit.org/trunk/toolname
300 Where "toolname" can be replaced with the name (or fragment of a name)
302 of any of the Maatkit tools. Once downloaded, they're ready to run; no
303 installation is needed. The first URL gets the latest released version
304 of the tool, and the second gets the latest trunk code from Subversion.
305
307 The environment variable "MKDEBUG" enables verbose debugging output in
308 all of the Maatkit tools:
309 MKDEBUG=1 mk-....
311
313 You need the following Perl modules: DBI and DBD::mysql.
314
316 For a list of known bugs see
317 <http://www.maatkit.org/bugs/mk-duplicate-key-checker>.
318 Please use Google Code Issues and Groups to report bugs or request
320 support: <http://code.google.com/p/maatkit/>. You can also join
321 #maatkit on Freenode to discuss Maatkit.
322 Please include the complete command-line used to reproduce the problem
324 you are seeing, the version of all MySQL servers involved, the complete
325 output of the tool when run with "--version", and if possible,
326 debugging output produced by running with the "MKDEBUG=1" environment
327 variable.
328
330 This program is copyright 2007-2011 Baron Schwartz. Feedback and
331 improvements are welcome.
332 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
334 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
335 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
336 This program is free software; you can redistribute it and/or modify it
338 under the terms of the GNU General Public License as published by the
339 Free Software Foundation, version 2; OR the Perl Artistic License. On
340 UNIX and similar systems, you can issue `man perlgpl' or `man
341 perlartistic' to read these licenses.
342 You should have received a copy of the GNU General Public License along
344 with this program; if not, write to the Free Software Foundation, Inc.,
345 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
346
348 Baron Schwartz, Daniel Nichter
349
351 This tool is part of Maatkit, a toolkit for power users of MySQL.
352 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
353 primary code contributors. Both are employed by Percona. Financial
354 support for Maatkit development is primarily provided by Percona and
355 its clients.
356
358 This manual page documents Ver 1.2.15 Distrib 7540 $Revision: 7477 $.
359perl v5.28.1 2011-06-08 MK-DUPLICATE-KEY-CHECKER(1)