MK-QUERY-ADVISOR(1)

📦
version 7540 · 3 versions
, in fedora 43

💖 MK-QUERY-ADVISOR(1) 💖

okay so bestie, welcome to MK-QUERY-ADVISOR(1) — User Contributed Perl Documentation edition. The header literally repeats the manual name twice, which is very main character of it. This is part 1, and there’s more coming, so don’t go anywhere. ✨

🌸 NAME 🌸

mk-query-advisor — analyze queries and advise on possible problems. It’s giving “SQL bestie who reads your queries and tells you what’s lowkey a red flag.” 💅

✨ SYNOPSIS ✨

Usage: mk-query-advisor [OPTION...] [FILE]

mk-query-advisor analyzes queries and advises on possible problems. Queries are given either by specifying slowlog files, --query, or --review. No cap.

# Analyzer all queries in the given slowlog
mk-query-advisor /path/to/slow-query.log

# Get queries from tcpdump using mk-query-digest
mk-query-digest --type tcpdump.txt --print --no-report | mk-query-advisor

# Get queries from a general log
mk-query-advisor --type genlog mysql.log

⚠️ RISKS ⚠️

This section is here to inform users about the potential risks, whether known or unknown, of using this tool. The two main categories of risks are those created by the nature of the tool (e.g. read-only tools vs. read-write tools) and those created by bugs.

mk-query-advisor simply reads queries and examines them, and is thus very low risk. low risk queen behavior.

At the time of this release there is a bug that may cause an infinite (or very long) loop when parsing very large queries. So yeah, heads up. 🫠

The authoritative source for updated information is always the online issue tracking system. Issues that affect this tool will be marked as such. You can see a list of such issues at the following URL:

<http://www.maatkit.org/bugs/mk-query-advisor>

See also "BUGS" for more information on filing bugs and getting help.

💅 DESCRIPTION 💅

mk-query-advisor examines queries and applies rules to them, trying to find queries that look bad according to the rules. It reports on queries that match the rules, so you can find bad practices or hidden problems in your SQL. By default, it accepts a MySQL slow query log as input. periodt.

👑 RULES 👑

These are the rules that mk-query-advisor will apply to the queries it examines. Each rule has three bits of information: an ID, a severity and a description.

The rule’s ID is its identifier. We use a seven-character ID, and the naming convention is three characters, a period, and a three-digit number. The first three characters are sort of an abbreviation of the general class of the rule. For example, ALI.001 is some rule related to how the query uses aliases.

The rule’s severity is an indication of how important it is that this rule matched a query. We use NOTE, WARN, and CRIT to denote these levels.

The rule’s description is a textual, human-readable explanation of what it means when a query matches this rule. Depending on the verbosity of the report you generate, you will see more of the text in the description. By default, you’ll see only the first sentence, which is sort of a terse synopsis of the rule’s meaning. At a higher verbosity, you’ll see subsequent sentences.

ALI.001

  • severity: note

Aliasing without the AS keyword. Explicitly using the AS keyword in column or table aliases, such as tbl AS alias, is more readable than implicit aliases such as tbl alias.

ALI.002

  • severity: warn

Aliasing the '*' wildcard. Aliasing a column wildcard, such as SELECT tbl.* col1, col2 probably indicates a bug in your SQL. You probably meant for the query to retrieve col1, but instead it renames the last column in the *-wildcarded list.

ALI.003

  • severity: note

Aliasing without renaming. The table or column’s alias is the same as its real name, and the alias just makes the query harder to read.

ARG.001

  • severity: warn

Argument with leading wildcard. An argument has a leading wildcard character, such as %foo. The predicate with this argument is not sargable and cannot use an index if one exists.

ARG.002

  • severity: note

LIKE without a wildcard. A LIKE pattern that does not include a wildcard is potentially a bug in the SQL.

CLA.001

  • severity: warn

SELECT without WHERE. The SELECT statement has no WHERE clause.

CLA.002

  • severity: note

ORDER BY RAND(). ORDER BY RAND() is a very inefficient way to retrieve a random row from the results.

CLA.003

  • severity: note

LIMIT with OFFSET. Paginating a result set with LIMIT and OFFSET is O(n^2) complexity, and will cause performance problems as the data grows larger.

CLA.004

  • severity: note

Ordinal in the GROUP BY clause. Using a number in the GROUP BY clause, instead of an expression or column name, can cause problems if the query is changed.

CLA.005

  • severity: warn

ORDER BY constant column.

CLA.006

  • severity: warn

GROUP BY or ORDER BY different tables will force a temp table and filesort.

CLA.007

  • severity: warn

ORDER BY different directions prevents index from being used. All tables in the ORDER BY clause must be either ASC or DESC, else MySQL cannot use an index.

COL.001

  • severity: note

SELECT *. Selecting all columns with the * wildcard will cause the query’s meaning and behavior to change if the table’s schema changes, and might cause the query to retrieve too much data.

COL.002

  • severity: note

Blind INSERT. The INSERT or REPLACE query doesn’t specify the columns explicitly, so the query’s behavior will change if the table’s schema changes; use INSERT INTO tbl(col1, col2) VALUES... instead.

LIT.001

  • severity: warn

Storing an IP address as characters. The string literal looks like an IP address, but is not an argument to INET_ATON(), indicating that the data is stored as characters instead of as integers. It is more efficient to store IP addresses as integers.

LIT.002

  • severity: warn

Unquoted date/time literal. A query such as WHERE col<2010-02-12 is valid SQL but is probably a bug; the literal should be quoted.

KWR.001

  • severity: note

SQL_CALC_FOUND_ROWS is inefficient. SQL_CALC_FOUND_ROWS can cause performance problems because it does not scale well; use alternative strategies to build functionality such as paginated result screens.

JOI.001

  • severity: crit

Mixing comma and ANSI joins. Mixing comma joins and ANSI joins is confusing to humans, and the behavior differs between some MySQL versions.

JOI.002

  • severity: crit

A table is joined twice. The same table appears at least twice in the FROM clause.

JOI.003

  • severity: warn

Reference to outer table column in WHERE clause prevents OUTER JOIN, implicitly converts to INNER JOIN.

JOI.004

  • severity: warn

Exclusion join uses wrong column in WHERE. The exclusion join (LEFT OUTER JOIN with a WHERE clause that is satisfied only if there is no row in the right-hand table) seems to use the wrong column in the WHERE clause. A query such as "... FROM l LEFT OUTER JOIN r ON l.l=r.r WHERE r.z IS NULL" probably ought to list r.r in the WHERE IS NULL clause.

RES.001

  • severity: warn

Non-deterministic GROUP BY. The SQL retrieves columns that are neither in an aggregate function nor the GROUP BY expression, so these values will be non-deterministic in the result.

RES.002

  • severity: warn

LIMIT without ORDER BY. LIMIT without ORDER BY causes non-deterministic results, depending on the query execution plan.

STA.001

  • severity: note

!= is non-standard. Use the <> operator to test for inequality.

SUB.001

  • severity: crit

IN() and NOT IN() subqueries are poorly optimized. MySQL executes the subquery as a dependent subquery for each row in the outer query. This is a frequent cause of serious performance problems. This might change version 6.0 of MySQL, but for versions 5.1 and older, the query should be rewritten as a JOIN or a LEFT OUTER JOIN, respectively.

✨ OPTIONS ✨

Okay bestie, quick heads-up before we dive in: --query and --review are mutually exclusive. Like, pick one. They can't both come to the party. 💅

Also this tool accepts additional command-line arguments (yes, more than what's listed here), so go peek at the SYNOPSIS and the usage info for the deets.

  • --ask-pass — Prompts you for a password when connecting to MySQL. So your password never has to touch the command line, cute and secure. 🎀
  • --charset — short form: -A; type: string. The default character set. And here's the tea: if the value is utf8, it sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL. Slay. ✨
  • --config — type: Array. Read this comma-separated list of config files. If you specify it, it must be the first option on the command line. First. No exceptions. 👑
  • --[no]continue-on-error — default: yes. Keeps working even if there's an error. Resilient queen behavior. 💪
  • --daemonize — Forks to the background and detaches from the shell. POSIX operating systems only. 🧚
  • --database — short form: -D; type: string. Connect to this database. It's also used as the default database for --[no]show-create-table if a query doesn't use database-qualified tables. Multi-purpose, main character energy. 🌟
  • --defaults-file — short form: -F; type: string. Only read mysql options from the given file. You must give an absolute pathname. No shortcuts bestie. 🚫
  • --group-by — type: string; default: rule_id. Groups the items in the report by this attribute. The possible attributes are:
    • rule_id — Items matching the same rule ID.
    • query_id — Queries with the same ID (the same fingerprint).
    • none — No grouping, report each query and its advice individually.
  • --help — Shows help and exits. Your little lifesaver. 🫶
  • --host — short form: -h; type: string. Connect to host.
  • --ignore-rules — type: hash. Ignore these rule IDs. Give it a comma-separated list of rule IDs (like LIT.001,RES.002,etc.) to ignore. Heads up though: currently the rule IDs are case-sensitive and must be uppercase. 💀
  • --password — short form: -p; type: string. Password to use when connecting.
  • --pid — type: string. Creates the given PID file when daemonized. That file holds the process ID of the daemonized instance. The PID file gets removed when the daemonized instance exits. When starting up, the program checks whether the PID file already exists; if it does and a process with the matching PID exists, the program exits. It's giving bouncer at the club. 🚪
  • --port — short form: -P; type: int. Port number to use for connection.
  • --print-all — Prints all queries, even the ones that don't match any rules. With --group-by none, non-matching queries get printed in the main report and the profile. For other --group-by values, non-matching queries are only printed in the profile. And those non-matching queries have zeros for NOTE, WARN and CRIT in the profile. No cap. 🌈
  • --query — type: string. Analyze this single query and ignore files and STDIN. This option lets you supply one single query right on the command line. Any files also specified on the command line are ignored. It said what it said. 💅
  • --report-format — type: string; default: compact. Type of report format: full or compact. In full mode, every query's report contains the description of the rules it matched, even if that info was already displayed before. In compact mode, the repeated info is suppressed and only the rule ID is displayed. Compact is the low-key efficient queen. 🎀
  • --review — type: DSN. Analyze queries from this mk-query-digest query review table.
  • --sample — type: int; default: 1. How many samples of the query to show.
  • --set-vars — type: string; default: wait_timeout=10000. Set these MySQL variables. Immediately after connecting to MySQL, this string gets appended to SET and executed. Set it and forget it. ✨
  • --[no]show-create-table — default: yes. Get SHOW CREATE TABLE for each query's table.

If host connection options are given (like --host, --port, etc.) then the tool will also get SHOW CREATE TABLE for each query. This info is needed for some rules like JOI.004. If you disable this option by specifying --no-show-create-table, then some rules may not be checked. Your call bestie, but them rules gonna be sitting there unverified. 😭

  • --socket — short form: -S; type: string. Socket file to use for connection.
  • --type — type: Array. The type of input to parse (default slowlog). The permitted types are slowlog and genlog.
  • --user — short form: -u; type: string. User for login if not the current user.
  • --verbose — short form: -v; cumulative: yes; default: 1. Increase verbosity of output. At the default level of verbosity, the program prints only the first sentence of each rule's description. At higher levels, the program prints more of the description. See also --report-format. Stack them -vs, go off. 🔥
  • --version — Shows version and exits.
  • --where — type: string. Apply this WHERE clause to the SELECT query on the --review table.

🦋 DSN OPTIONS 🦋

These DSN options are used to create a DSN. Each option is given like option=value. These options are case-sensitive, so P and p are not the same option. There cannot be whitespace before or after the =, and if the value contains whitespace it must be quoted. DSN options are comma-separated. See the maatkit manpage for full details. 💜

  • A — dsn: charset; copy: yes. Default character set.
  • D — dsn: database; copy: yes. Database that contains the query review table.
  • F — dsn: mysql_read_default_file; copy: yes. Only read default options from the given file.
  • h — dsn: host; copy: yes. Connect to host.
  • p — dsn: password; copy: yes. Password to use when connecting.
  • P — dsn: port; copy: yes. Port number to use for connection.
  • S — dsn: mysql_socket; copy: yes. Socket file to use for connection.
  • t — Table to use as the query review table.
  • u — dsn: user; copy: yes. User for login if not the current user.

🌸 DOWNLOADING 🌸

You can download Maatkit from Google Code at <http://code.google.com/p/maatkit/>, or you can grab any of the tools super easily with a command like this:

wget http://www.maatkit.org/get/toolname
or
wget http://www.maatkit.org/trunk/toolname

Where toolname can be replaced with the name (or a fragment of a name) of any of the Maatkit tools. And once they're downloaded, they're ready to run — no installation needed, we love a low-maintenance icon. 🧚

The first URL gets you the latest released version of the tool, and the second gets you the latest trunk code from Subversion. Pick your vibe. ✨

🎀 ENVIRONMENT 🎀

The environment variable MKDEBUG enables verbose debugging output in all of the Maatkit tools:

MKDEBUG=1 mk-....

⭐ SYSTEM REQUIREMENTS ⭐

You need the following Perl modules: DBI and DBD::mysql.

💀 BUGS 💀

For a list of known bugs, see <http://www.maatkit.org/bugs/mk-query-advisor>.

Please use Google Code Issues and Groups to report bugs or request support: <http://code.google.com/p/maatkit/>. You can also join #maatkit on Freenode to discuss Maatkit.

And when you report something, bestie, please include the complete command-line you used to reproduce the problem, the version of all MySQL servers involved, the complete output of the tool when run with --version, and if possible, the debugging output produced by running with the MKDEBUG=1 environment variable. Give us the whole receipt, not just a screenshot. 📸

This program is copyright 2009-2011 Percona Inc. Feedback and improvements are welcome. 🫶

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Bestie that means no promises, ok? Like, zero. It's giving "you're on your own" but in a legal way. 😭

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic License. On UNIX and similar systems, you can issue man perlgpl or man perlartistic to read these licenses.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.

💖 AUTHOR 💖

Baron Schwartz, Daniel Nichter

🌟 ABOUT MAATKIT 🌟

This tool is part of Maatkit, a toolkit for power users of MySQL. Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the primary code contributors. Both are employed by Percona. Financial support for Maatkit development is primarily provided by Percona and its clients.

🌈 VERSION 🌈

This manual page documents Ver 1.0.4 Distrib 7540 $Revision: 7531 $.

And that's the whole page, bestie! 💅 This one's rendered by perl v5.42.0 and dated 2025-07-24, and yes it's still filed under MK-QUERY-ADVISOR(1) — section one, where all the good stuff lives. Go forth and advise some queries. Periodt. 🎀✨🦋

📦 where this page comes from