GoAdmio

index

Add missing indexes to MariaDB/MySQL columns found in logged queries

On this page: Overview · Quick start · Syntax · Output · Examples · Troubleshooting · Details · See also


Overview

/go/index speeds up MariaDB and MySQL by adding indexes that your sites' queries could use. It:

If query logging is not running yet, the command starts it and asks you to try again later.

[!WARNING] Adding an index to a large table can take time and slow the site while it runs. Run /go/index when traffic is low.


Quick start

# Add missing indexes (run as root)
/go/index

On the first run, the command may only start query logging. Run it again after 5 minutes.


Syntax

/go/index

The command takes no arguments or options. Run it as root.


Output

Message Meaning
Start MariaDB logging... The query log is missing, empty, or older than one hour. Logging is being started with /go/my --log.
Try again after 5 minutes. Run the command again once the log has collected queries. The command exits with status 1.
ALTER TABLE <db>.<table> ADD INDEX <column> (<column>) An index is being added. One line is printed for each index.

If nothing needs indexing, the command prints nothing.


Examples

# Add missing indexes
/go/index
# Review the log of past runs
tail -n 20 /var/log/go/index.log
# Check the indexes on a table afterwards (replace the names)
mariadb -e "SHOW INDEX FROM mydb.mytable"
# Remove an index this command added (each index is named after its column)
mariadb -e "ALTER TABLE mydb.mytable DROP INDEX mycolumn"

Troubleshooting

Symptom Likely cause Fix
Start MariaDB logging... and Try again after 5 minutes. /var/log/mysql.log is missing, has 10 bytes or fewer, or was last written more than an hour ago. Wait 5 minutes while the sites run queries, then run again.
Nothing is printed The columns found already have indexes, or none of the names could be matched. None.
A column with an underscore, such as user_id, is never indexed Underscores are removed from names before the lookup, so the name no longer exists. See Name limits. Add the index yourself with ALTER TABLE.
An index was added in a database you did not expect The log does not record which database a query used. See How matching works. Drop the index with ALTER TABLE ... DROP INDEX.
Access denied for user root, or a password prompt The script needs the MySQLRootPassword variable to be set. Export the variable in the environment that runs the command.
The site is slow while /go/index runs An index is being built on a large table. Run it during low traffic.

Details

Query log requirement

/var/log/mysql.log must exist, be larger than 10 bytes, and have been modified within the last hour. If not, the command runs /go/my --log to start logging and exits with status 1. See my.

When the log is ready, the command copies it to /usr/local/goadmio/tmp/mysql.log and works on the copy.

How matching works

  1. Upper-case WHERE, AND, OR, and ORDER BY in the copy are changed to lower case.
  2. Table and column pairs are extracted from the patterns below and written to /usr/local/goadmio/tmp/index.db.
  3. Duplicates are removed into /usr/local/goadmio/tmp/columns.db.
  4. For every database except mysql, sys, information_schema, performance_schema, roundcube, and opendmarc, each pair is checked.
  5. If the table has that column and the column is not part of any index yet, the index is added.
Pattern in the log Column taken
table where a The first column after where.
table where a=... and b (up to four and conditions) The column after the last and.
table where a=... or b (up to four or conditions) The column after the last or.
table where ... order by c The column after order by.

The word just before where is taken as the table name, so queries with an alias or a join in that position produce names that do not exist. Those are skipped.

[!NOTE] The ORDER BY step runs last and replaces the list built by the earlier steps. As the script is written today, only the ORDER BY columns reach the indexing step.

The log does not say which database a query ran against. Every pair is therefore tried in every database, so a table and column that exist in more than one database are indexed in each of them.

Name limits

Table and column names are reduced to letters and numbers before the lookup. Underscores are removed, so names such as wp_posts or user_id never match and are skipped.

The index that is added

Property Value
Statement ALTER TABLE <db>.<table> ADD INDEX <column> (<column>)
Index name The column name
Columns One column per index
Skipped when The column is already part of any index, or the table or column does not exist.

Requirements

Item Detail
Privileges Run as root.
MySQLRootPassword Must be set in the environment. The script does not set it itself.
Logging MariaDB query logging to /var/log/mysql.log.

What a run does

flowchart TD
    A["index"] --> B{"mysql.log exists, over 10 bytes, changed in last hour?"}
    B -- "No" --> C["Start logging with my --log"]
    C --> X["Try again after 5 minutes, exit 1"]
    B -- "Yes" --> D["Copy the log"]
    D --> E["Extract table and column names"]
    E --> F["For each user database and each pair"]
    F --> G{"Column exists and has no index?"}
    G -- "Yes" --> H["ALTER TABLE ... ADD INDEX"]
    G -- "No" --> I["Skip"]

See also

Command Description
my Optimize the my.cnf configuration for MySQL/MariaDB
querytop Show the most frequently executed MySQL SQL queries
mysqltop Identify the most accessed databases on a GoAdmio server
dbls List all databases and their sizes
repair Repair a MySQL database