MK-TABLE-USAGE(1)

📦

version 7540 · 3 versions

💖 MK-TABLE-USAGE 💖 (mk-table-usage, from the User Contributed Perl Documentation)

okay bestie, buckle UP, because we're about to read some slow query logs and figure out exactly how they be using tables 💅 this is mk-table-usage and she is a whole vibe.

✨ NAME ✨

mk-table-usage — read queries from a log and analyze how they use tables.

🌸 SYNOPSIS 🌸

Usage: mk-table-usage [OPTION...] [FILE...]

mk-table-usage reads queries from slow query logs and analyzes how they use tables. If no FILE is specified, STDIN is read. Table usage for every query is printed to STDOUT.

🫶 RISKS 🫶

ngl, mk-table-use is very low risk, because she literally just reads and examines queries from a log, and only runs EXPLAIN EXTENDED if you passed the --explain-extended option. that's it. that's the whole risk profile. she's harmless, she's chill, she's not gonna touch your data 🧚

At the time of this release, there are no known bugs that could cause serious harm to users.

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-table-usage>.

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

👑 DESCRIPTION 👑

mk-table-usage reads queries from slow query logs and analyzes how they use tables. And when we say "how they use tables," we mean way more than just which tables get read from or written to — it's about data flow: data in and data out 🌈. Data flow gets determined by the contexts in which tables are used by the query. And here's the gag: a single table can show up in several different contexts in the same query. The reported table usage for each query lists every context for every table. That CONTEXT-TABLE list tells you how and where data flows, i.e. the query's table usage. The "OUTPUT" section (below, keep scrolling bestie) lists the possible contexts and describes how to read a table usage report.

Since this tool analyzes table usage, it's important that queries use table-qualified columns. If a query uses only one table, then all columns must be from that table and there's no problem, we love that for you 💖. But if a query uses multiple tables and the columns are not table-qualified, then that creates a problem that can only be solved by knowing the query's database and specifying --explain-extended. If the slow log does not specify the database used by the query, then you can specify a default database with --database. There is no other way to know or guess the database, so the query will be skipped. Secondly, if the database is known, then specifying --explain-extended causes mk-table-usage to do EXPLAIN EXTENDED ... + SHOW WARNINGS to get the fully qualified query as reported by MySQL (i.e. all identifiers are fully database- and/or table-qualified). For best results, you should specify --explain-extended and --database if you know that all queries use the same database.

Each query is identified in the output by either an MD5 hex checksum of the query's fingerprint or the query's value for the specified --id-attribute. The query ID is for parsing and storing the table usage reports in a table that is keyed on the query ID. See "OUTPUT" for more information.

🦋 OUTPUT 🦋

The table usage report that is printed for each query looks similar to the following:

Query_id: 0x1CD27577D202A339.1
UPDATE t1
SELECT DUAL
JOIN t1
JOIN t2
WHERE t1

Query_id: 0x1CD27577D202A339.2
UPDATE t2
SELECT DUAL
JOIN t1
JOIN t2
WHERE t1

Usage reports are separated by blank lines. The first line is always the query ID: a unique ID that can be used to parse the output and store the usage reports in a table keyed on this ID. The query ID has two parts separated by a period: the query ID and the target table number.

If --id-attribute is not specified, then query IDs are automatically created by making an MD5 hex checksum of the query's fingerprint (as shown above, e.g. 0x1CD27577D202A339); otherwise, the query ID is the query's value for the given attribute.

The target table number starts at 1 and increments by 1 for each table that the query affects. Only multi-table UPDATE queries can affect multiple tables with a single query, so this number is 1 for all other types of queries. (Multi-table DELETE queries are not supported.) The example output above is from this query:

UPDATE t1 AS a JOIN t2 AS b USING (id)
SET a.foo="bar", b.foo="bat"
WHERE a.id=1;

The SET clause indicates that two tables are updated: a aliased as t1, and b aliased as t2. So two usage reports are printed, one for each table, and this is indicated in the output by their common query ID but incrementing target table number. iconic behavior honestly ✨

After the first line is a variable number of CONTEXT-TABLE lines. Possible contexts are:

SELECT — SELECT means that data is taken out of the table for one of two reasons: to be returned to the user as part of a result set, or to be put into another table as part of an INSERT or UPDATE. In the first case, since only SELECT queries return result sets, a SELECT context is always listed for SELECT queries. In the second case, data from one table is used to insert or update rows in another table. For example, the UPDATE query in the example above has the usage:

SELECT DUAL

This refers to:

SET a.foo="bar", b.foo="bat"

DUAL is used for any values that does not originate in a table, in this case the literal values "bar" and "bat". If that SET clause were SET a.foo=b.foo instead, then the complete usage would be:

Query_id: 0x1CD27577D202A339.1
UPDATE t1
SELECT t2
JOIN t1
JOIN t2
WHERE t1

The presence of a SELECT context after another context, such as UPDATE or INSERT, indicates where the UPDATE or INSERT retrieves its data. The example immediately above reflects an UPDATE query that updates rows in table t1 with data from table t2.

Any other query type — Any other query type, such as INSERT, UPDATE, DELETE, etc. may be a context. All these types indicate that the table is written or altered in some way. If a SELECT context follows one of these types, then data is read from the SELECT table and written to this table. This happens, for example, with INSERT..SELECT or UPDATE queries that set column values using values from tables instead of constant values.

These query types are not supported:

SET
LOAD
multi-table DELETE

JOIN — The JOIN context lists tables that are joined, either with an explicit JOIN in the FROM clause, or implicitly in the WHERE clause, such as t1.id = t2.id.
WHERE — The WHERE context lists tables that are used in the WHERE clause to filter results. This does not include tables that are implicitly joined in the WHERE clause; those are listed as JOIN contexts. For example:

WHERE t1.id > 100 AND t1.id < 200 AND t2.foo IS NOT NULL

Results in:

WHERE t1
WHERE t2

Only unique tables are listed; that is why table t1 is listed only once.

TLIST — The TLIST context lists tables that are accessed by the query but do not appear in any other context. These tables are usually an implicit full cartesian join, so they should be avoided. For example, the query SELECT * FROM t1, t2 results in:

Query_id: 0xBDDEB6EDA41897A8.1
SELECT t1
SELECT t2
TLIST t1
TLIST t2

First of all, there are two SELECT contexts, because SELECT * selects rows from all tables; t1 and t2 in this case. Secondly, the tables are implicitly joined, but without any kind of join condition, which results in a full cartesian join as indicated by the TLIST context for each.

bestie, lowkey if you see TLIST in your output, that's your sign from the universe that something needs fixing 💀 full cartesian joins are NOT the moment.

💅 EXIT STATUS 💅

mk-table-usage exits 1 on any kind of error, or 0 if no errors. periodt. 🎀

✨ OPTIONS ✨

okay so this tool takes extra command-line arguments beyond what you already saw, bestie 💖 — peep the SYNOPSIS and the usage info for the deets.

--ask-pass — it'll prompt you for a password when connecting to MySQL. cute and secure, we love that 🎀
--charset — short form: -A; type: string

this is the default character set. 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.

--config — type: Array

read this comma-separated list of config files. heads up bestie: if you specify it, it must be the first option on the command line. no exceptions, that's the rule 💅

--constant-data-value — type: string; default: DUAL

the value to print for constant data. and "constant data" means literally all data that isn't from tables (or subqueries, since subqueries are just not supported here, sorry 🙈). so like, real constant values like strings ("foo") and numbers (42), plus data from functions like NOW(). for example, in the query INSERT INTO t (c) VALUES ('a'), that string 'a' is constant data, so the table usage report is:

INSERT t
SELECT DUAL

the first line says data is inserted into table t, and the second line says that data comes from some constant value. see? makes sense 🫶

--[no]continue-on-error — default: yes

keep on parsing even if there's an error. it's giving "we don't stop, we keep going" energy ⭐

--create-table-definitions — type: array

read CREATE TABLE definitions from this list of comma-separated files. so if you can't use --explain-extended to fully qualify table and column names, you can save the output of mysqldump --no-data to one or more files and point this option at them. the tool parses all CREATE TABLE definitions from the files and uses that info to qualify table and column names. BUT ⚠️ — if a column name shows up in multiple tables, or a table name is used in multiple databases, those duplicates just can't be qualified. it's giving "we tried our best" 😭

--daemonize — fork to the background and detach from the shell. POSIX operating systems only, bestie, don't try it anywhere else.
--database — short form: -D; type: string

the default database.

--defaults-file — short form: -F; type: string

only read MySQL options from the given file. and you must give an absolute pathname, no relative shenanigans 🔥

--explain-extended — type: DSN

run EXPLAIN EXTENDED queries on this host to fully qualify table and column names. slay.

--filter — type: string

discard events for which this Perl code doesn't return true.

okay so this option is a string of Perl code, or a file containing Perl code, that gets compiled into a subroutine with one argument: $event. that's a hashref, btw. if the given value is a readable file, then mk-query-digest reads the entire file and uses its contents as the code. and bestie, the file should not contain a shebang (#!/usr/bin/perl) line. noted 💅

if the code returns true, the chain of callbacks continues; otherwise it ends. the code is the last statement in the subroutine other than return $event. the subroutine template is:

sub { $event = shift; filter && return $event; }

filters given on the command line are wrapped inside parentheses like ( filter ). for complex, multi-line filters, you must put the code inside a file so it won't get wrapped inside parentheses. either way, the filter has to produce syntactically valid code given the template. for example, an if-else branch given on the command line would not be valid:

--filter 'if () { } else { }'  # WRONG

since it's given on the command line, the if-else branch would get wrapped inside parentheses, which is not syntactically valid. so to do something more complex like that, you'd need to put the code in a file, for example filter.txt:

my $event_ok; if (...) { $event_ok=1; } else { $event_ok=0; } $event_ok

then specify --filter filter.txt to read the code from filter.txt. 💖

if the filter code won't compile, mk-query-digest will die with an error. and even if the filter code does compile, an error may still occur at runtime if the code tries to do something wrong (like pattern match an undefined value). mk-query-digest does not provide any safeguards, so code carefully! 😭

an example filter that discards everything but SELECT statements:

--filter '$event->{arg} =~ m/^select/i'

this is compiled into a subroutine like the following:

sub { $event = shift; ( $event->{arg} =~ m/^select/i ) && return $event; }

it is permissible for the code to have side effects (to alter $event). ✨

you can find an explanation of the structure of $event at http://code.google.com/p/maatkit/wiki/EventAttributes.

here are more examples of filter code, bestie 👑

Host/IP matches domain.com

--filter '($event->{host} || $event->{ip} || "") =~
m/domain.com/'

sometimes MySQL logs the host where the IP is expected. therefore, we check both. 🫶

User matches john

--filter '($event->{user} || "") =~ m/john/'

More than 1 warning

--filter '($event->{Warning_count} || 0) > 1'

Query does full table scan or full join

--filter '(($event->{Full_scan} || "") eq "Yes") ||
(($event->{Full_join} || "") eq "Yes")'

Query was not served from query cache

--filter '($event->{QC_Hit} || "") eq "No"'

Query is 1 MB or larger

--filter '$event->{bytes} >= 1_048_576'

and since --filter lets you alter $event, you can use it to do other things too, like create new attributes. main character energy fr fr 💅

--help — show help and exit. simple as that 🌸
--host — short form: -h; type: string

connect to host.

--id-attribute — type: string

identify each event using this attribute. if no ID attribute is given, then events get identified with the query's checksum: an MD5 hex checksum of the query's fingerprint.

--log — type: string

print all output to this file when daemonized. 📝

--password — short form: -p; type: string

password to use when connecting.

--pid — type: string

create the given PID file when running. the file contains the process ID of the daemonized instance, and it gets removed when the daemonized instance exits. the program checks for the existence of the PID file when starting; if it exists and the process with the matching PID exists, the program exits. so don't be out here running duplicates, bestie 😤

--port — short form: -P; type: int

port number to use for connection.

--progress — type: array; default: time,30

print progress reports to STDERR. the value is a comma-separated list with two parts. the first part can be percentage, time, or iterations; the second part specifies how often an update should be printed, in percentage, seconds, or number of iterations. cute lil status updates, we love to see it ✨

--query — type: string

analyze only this given query. so if you want to analyze the table usage of one simple query by providing it on the command line instead of reading it from a slow log file, then specify that query with this option. the default --id-attribute will be used, which is the query's checksum. 🎀

--read-timeout — type: time; default: 0

wait this long for an event from the input; 0 to wait forever.

this option sets the maximum time to wait for an event from the input. if an event is not received after the specified time, the script stops reading the input and prints its reports.

this option requires the Perl POSIX module. iykyk 💜

--run-time — type: time

how long to run before exiting. the default is to run forever (you can interrupt with CTRL-C). no cap.

--set-vars — type: string; default: wait_timeout=10000

set these MySQL variables. immediately after connecting to MySQL, this string will be appended to SET and executed.

--socket — short form: -S; type: string

socket file to use for connection.

--user — short form: -u; type: string

user for login if not current user.

--version — show version and exit. ⭐

🌈 DSN OPTIONS 🌈

these DSN options are used to create a DSN. each option is given like option=value. and bestie, listen: the 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.
u — dsn: user; copy: yes — User for login if not 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 the following:

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

where toolname can be replaced with the name (or fragment of a name) of any of the Maatkit tools. once downloaded, they're ready to run; no installation needed, bestie, we love a low-effort slay 🫶. the first URL gets the latest released version of the tool, and the second gets the latest trunk code from Subversion.

🌱 ENVIRONMENT 🌱

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

MKDEBUG=1 mk-....

🖥️ SYSTEM REQUIREMENTS 🖥️

you need Perl and some core packages that ought to be installed in any reasonably new version of Perl. so like, probably fine already, but check 💅

💀 BUGS 💀

for a list of known bugs see http://www.maatkit.org/bugs/mk-table-usage.

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. 👑

please include the complete command-line used to reproduce the problem you're seeing, the version of all MySQL servers involved, the complete output of the tool when run with --version, and if possible, debugging output produced by running with the MKDEBUG=1 environment variable. help us help you, bestie 🙏✨

💖 COPYRIGHT, LICENSE AND WARRANTY 💖

okay bestie, legal era, but it's short and it's important so let's go 🫶

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. 💀 no cap, if it breaks it's on you, that's the whole vibe.

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. Pick your fighter, either one works 💅

On UNIX and similar systems, you can issue:

man perlgpl

man perlartistic

to read these licenses. ✨ (two whole manuals, just for the legal tea, iconic)

You should have received a copy of the GNU General Public License along with this program; if not, write to:

Free Software Foundation, Inc.
59 Temple Place, Suite 330
Boston, MA  02111-1307  USA

👑 AUTHOR

Daniel Nichter — the main character who wrote this, we stan 🌸

🌈 VERSION

This manual page documents Ver 1.0.1 Distrib 7540 $Revision: 7531 $. fr fr, that's the receipt 🧾

and that's a wrap on the whole page, bestie 🎀 you just read the entire thing, MK-TABLE-USAGE(1), documented as perl v5.42.0 back on 2025-07-24 🦋 go forth and table-usage responsibly, mwah 💖✨

📦 where this page comes from

fedora 43: maatkit 7540-39.fc43 · GPL-2.0-or-later OR Artistic-1.0-Perl
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-10-18: a language model's rewrite of the original, under its terms