1MK-QUERY-ADVISOR(1) User Contributed Perl Documentation MK-QUERY-ADVISOR(1)
2
6 mk-query-advisor - Analyze queries and advise on possible problems.
7
9 mk-query-advisor /path/to/slow-query.log
10 # Get queries from tcpdump using mk-query-digest
12 mk-query-digest --type tcpdump.txt --print --no-report | mk-query-advisor
13
15 The following section is included to inform users about the potential
16 risks, whether known or unknown, of using this tool. The two main
17 categories of risks are those created by the nature of the tool (e.g.
18 read-only tools vs. read-write tools) and those created by bugs.
19 mk-query-advisor simply reads queries and examines them, and is thus
21 very low risk.
22 At the time of this release, we know of no bugs that could cause
24 serious harm to users.
25 The authoritative source for updated information is always the online
27 issue tracking system. Issues that affect this tool will be marked as
28 such. You can see a list of such issues at the following URL:
29 http://www.maatkit.org/bugs/mk-query-advisor
30 <http://www.maatkit.org/bugs/mk-query-advisor>.
31 See also "BUGS" for more information on filing bugs and getting help.
33
35 mk-query-advisor examines queries and applies rules to them, trying to
36 find queries that look bad according to the rules. It reports on
37 queries that match the rules, so you can find bad practices or hidden
38 problems in your SQL. By default, it accepts a MySQL slow query log as
39 input.
40
42 These are the rules that mk-query-advisor will apply to the queries it
43 examines. Each rule has three bits of information: an ID, a severity
44 and a description.
45 The rule's ID is its identifier. We use a seven-character ID, and the
47 naming convention is three characters, a period, and a three-digit
48 number. The first three characters are sort of an abbreviation of the
49 general class of the rule. For example, ALI.001 is some rule related
50 to how the query uses aliases.
51 The rule's severity is an indication of how important it is that this
53 rule matched a query. We use NOTE, WARN, and CRIT to denote these
54 levels.
55 The rule's description is a textual, human-readable explanation of what
57 it means when a query matches this rule. Depending on the verbosity of
58 the report you generate, you will see more of the text in the
59 description. By default, you'll see only the first sentence, which is
60 sort of a terse synopsis of the rule's meaning. At a higher verbosity,
61 you'll see subsequent sentences.
62 ALI.001
64 severity: note
65 Aliasing without the AS keyword. Explicitly using the AS keyword
67 in column or table aliases, such as "tbl AS alias," is more
68 readable than implicit aliases such as "tbl alias".
69 ALI.002
71 severity: warn
72 Aliasing the '*' wildcard. Aliasing a column wildcard, such as
74 "SELECT tbl.* col1, col2" probably indicates a bug in your SQL.
75 You probably meant for the query to retrive col1, but instead it
76 renames the last column in the *-wildcarded list.
77 ALI.003
79 severity: note
80 Aliasing without renaming. The table or column's alias is the same
82 as its real name, and the alias just makes the query harder to
83 read.
84 ARG.001
86 severity: warn
87 Argument with leading wildcard. An argument has a leading wildcard
89 character, such as "%foo". The predicate with this argument is not
90 sargable and cannot use an index if one exists.
91 ARG.002
93 severity: note
94 LIKE without a wildcard. A LIKE pattern that does not include a
96 wildcard is potentially a bug in the SQL.
97 CLA.001
99 severity: warn
100 SELECT without WHERE. The SELECT statement has no WHERE clause.
102 CLA.002
104 severity: note
105 ORDER BY RAND(). ORDER BY RAND() is a very inefficient way to
107 retrieve a random row from the results.
108 CLA.003
110 severity: note
111 LIMIT with OFFSET. Paginating a result set with LIMIT and OFFSET
113 is O(n^2) complexity, and will cause performance problems as the
114 data grows larger.
115 CLA.004
117 severity: note
118 Ordinal in the GROUP BY clause. Using a number in the GROUP BY
120 clause, instead of an expression or column name, can cause problems
121 if the query is changed.
122 CLA.005
124 severity: warn
125 ORDER BY constant column.
127 COL.001
129 severity: note
130 SELECT *. Selecting all columns with the * wildcard will cause the
132 query's meaning and behavior to change if the table's schema
133 changes, and might cause the query to retrieve too much data.
134 COL.002
136 severity: note
137 Blind INSERT. The INSERT or REPLACE query doesn't specify the
139 columns explicitly, so the query's behavior will change if the
140 table's schema changes; use "INSERT INTO tbl(col1, col2) VALUES..."
141 instead.
142 LIT.001
144 severity: warn
145 Storing an IP address as characters. The string literal looks like
147 an IP address, but is not an argument to INET_ATON(), indicating
148 that the data is stored as characters instead of as integers. It
149 is more efficient to store IP addresses as integers.
150 LIT.002
152 severity: warn
153 Unquoted date/time literal. A query such as "WHERE col<2010-02-12"
155 is valid SQL but is probably a bug; the literal should be quoted.
156 KWR.001
158 severity: note
159 SQL_CALC_FOUND_ROWS is inefficient. SQL_CALC_FOUND_ROWS can cause
161 performance problems because it does not scale well; use
162 alternative strategies to build functionality such as paginated
163 result screens.
164 JOI.001
166 severity: crit
167 Mixing comma and ANSI joins. Mixing comma joins and ANSI joins is
169 confusing to humans, and the behavior differs between some MySQL
170 versions.
171 JOI.002
173 severity: crit
174 A table is joined twice. The same table appears at least twice in
176 the FROM clause.
177 RES.001
179 severity: warn
180 Non-deterministic GROUP BY. The SQL retrieves columns that are
182 neither in an aggregate function nor the GROUP BY expression, so
183 these values will be non-deterministic in the result.
184 RES.002
186 severity: warn
187 LIMIT without ORDER BY. LIMIT without ORDER BY causes non-
189 deterministic results, depending on the query execution plan.
190 STA.001
192 severity: note
193 != is non-standard. Use the <> operator to test for inequality.
195 SUB.001
197 severity: crit
198 IN() and NOT IN() subqueries are poorly optimized. MySQL executes
200 the subquery as a dependent subquery for each row in the outer
201 query. This is a frequent cause of serious performance problems.
202 This might change version 6.0 of MySQL, but for versions 5.1 and
203 older, the query should be rewritten as a JOIN or a LEFT OUTER
204 JOIN, respectively.
205
207 "--query" and "--review" are mutually exclusive.
208 --ask-pass
210 Prompt for a password when connecting to MySQL.
211 --charset
213 short form: -A; type: string
214 Default character set. If the value is utf8, sets Perl's binmode
216 on STDOUT to utf8, passes the mysql_enable_utf8 option to
217 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
218 other value sets binmode on STDOUT without the utf8 layer, and runs
219 SET NAMES after connecting to MySQL.
220 --config
222 type: Array
223 Read this comma-separated list of config files; if specified, this
225 must be the first option on the command line.
226 --[no]continue-on-error
228 default: yes
229 Continue working even if there is an error.
231 --daemonize
233 Fork to the background and detach from the shell. POSIX operating
234 systems only.
235 --defaults-file
237 short form: -F; type: string
238 Only read mysql options from the given file. You must give an
240 absolute pathname.
241 --help
243 Show help and exit.
244 --host
246 short form: -h; type: string
247 Connect to host.
249 --ignore-rules
251 type: hash
252 Ignore these rule IDs.
254 Specify a comma-separated list of rule IDs (e.g.
256 LIT.001,RES.002,etc.) to ignore. Currently, the rule IDs are case-
257 sensitive and must be uppercase.
258 --password
260 short form: -p; type: string
261 Password to use when connecting.
263 --pid
265 type: string
266 Create the given PID file when daemonized. The file contains the
268 process ID of the daemonized instance. The PID file is removed
269 when the daemonized instance exits. The program checks for the
270 existence of the PID file when starting; if it exists and the
271 process with the matching PID exists, the program exits.
272 --port
274 short form: -P; type: int
275 Port number to use for connection.
277 --print-all
279 Print all queries, even those that do not match any rules.
280 --query
282 type: string
283 Analyze this single query and ignore files and STDIN. This option
285 allows you to supply a single query on the command line. Any files
286 also specified on the command line are ignored.
287 --report-format
289 type: string; default: compact
290 Type of report format: full or compact. In full mode, every
292 query's report contains the description of the rules it matched,
293 even if this information was previously displayed. In compact
294 mode, the repeated information is suppressed, and only the rule ID
295 is displayed.
296 --review
298 type: DSN
299 Analyze queries from this mk-query-digest query review table.
301 --sample
303 type: int; default: 1
304 How many samples of the query to show.
306 --set-vars
308 type: string; default: wait_timeout=10000
309 Set these MySQL variables. Immediately after connecting to MySQL,
311 this string will be appended to SET and executed.
312 --socket
314 short form: -S; type: string
315 Socket file to use for connection.
317 --type
319 type: Array
320 The type of input to parse (default slowlog). The permitted types
322 are slowlog and genlog.
323 --user
325 short form: -u; type: string
326 User for login if not current user.
328 --verbose
330 short form: -v; cumulative: yes; default: 1
331 Increase verbosity of output. At the default level of verbosity,
333 the program prints only the first sentence of each rule's
334 description. At higher levels, the program prints more of the
335 description. See also "--report-format".
336 --version
338 Show version and exit.
339 --where
341 type: string
342 Apply this WHERE clause to the SELECT query on the "--review"
344 table.
345
347 These DSN options are used to create a DSN. Each option is given like
348 "option=value". The options are case-sensitive, so P and p are not the
349 same option. There cannot be whitespace before or after the "=" and if
350 the value contains whitespace it must be quoted. DSN options are
351 comma-separated. See the maatkit manpage for full details.
352 · A
354 dsn: charset; copy: yes
356 Default character set.
358 · D
360 dsn: database; copy: yes
362 Database that contains the query review table.
364 · F
366 dsn: mysql_read_default_file; copy: yes
368 Only read default options from the given file
370 · h
372 dsn: host; copy: yes
374 Connect to host.
376 · p
378 dsn: password; copy: yes
380 Password to use when connecting.
382 · P
384 dsn: port; copy: yes
386 Port number to use for connection.
388 · S
390 dsn: mysql_socket; copy: yes
392 Socket file to use for connection.
394 · t
396 Table to use as the query review table.
398 · u
400 dsn: user; copy: yes
402 User for login if not current user.
404
406 You can download Maatkit from Google Code at
407 <http://code.google.com/p/maatkit/>, or you can get any of the tools
408 easily with a command like the following:
409 wget http://www.maatkit.org/get/toolname
411 or
412 wget http://www.maatkit.org/trunk/toolname
413 Where "toolname" can be replaced with the name (or fragment of a name)
415 of any of the Maatkit tools. Once downloaded, they're ready to run; no
416 installation is needed. The first URL gets the latest released version
417 of the tool, and the second gets the latest trunk code from Subversion.
418
420 The environment variable "MKDEBUG" enables verbose debugging output in
421 all of the Maatkit tools:
422 MKDEBUG=1 mk-....
424
426 You need the following Perl modules: DBI and DBD::mysql.
427
429 For list of known bugs see http://www.maatkit.org/bugs/mk-query-advisor
430 <http://www.maatkit.org/bugs/mk-query-advisor>.
431 Please use Google Code Issues and Groups to report bugs or request
433 support: <http://code.google.com/p/maatkit/>. You can also join
434 #maatkit on Freenode to discuss Maatkit.
435 Please include the complete command-line used to reproduce the problem
437 you are seeing, the version of all MySQL servers involved, the complete
438 output of the tool when run with "--version", and if possible,
439 debugging output produced by running with the "MKDEBUG=1" environment
440 variable.
441
443 This program is copyright 2009-2010 Percona Inc. Feedback and
444 improvements are welcome.
445 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
447 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
448 MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
449 This program is free software; you can redistribute it and/or modify it
451 under the terms of the GNU General Public License as published by the
452 Free Software Foundation, version 2; OR the Perl Artistic License. On
453 UNIX and similar systems, you can issue `man perlgpl' or `man
454 perlartistic' to read these licenses.
455 You should have received a copy of the GNU General Public License along
457 with this program; if not, write to the Free Software Foundation, Inc.,
458 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
459
461 Baron Schwartz, Daniel Nichter
462
464 This tool is part of Maatkit, a toolkit for power users of MySQL.
465 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
466 primary code contributors. Both are employed by Percona. Financial
467 support for Maatkit development is primarily provided by Percona and
468 its clients.
469
471 This manual page documents Ver 1.0.0 Distrib 6839 $Revision: 6831 $.
472perl v5.12.1 2010-08-01 MK-QUERY-ADVISOR(1)