How sql_helper Automates Index Recommendations for MySQL & MariaDB

Tool Overview

sql_helper is an open-source project that offers both a command-line mode and a web interface. Its main function is to automatically determine whether condition fields in a query need an index, supporting MySQL 5.7/8.0 and MariaDB. By parsing SQL statements, it identifies fields that could benefit from indexing to improve query performance.

Workflow Steps

Parse the SQL with a syntax parser to extract table names, aliases, join fields, condition fields, order‑by fields, and group‑by fields.

Check for a WHERE clause; if absent, the tool issues a prompt.

For JOINs (e.g., a JOIN b ON a.id = b.id), examine the join columns in the table schema and suggest index creation if missing.

Run EXPLAIN; if the access type is ALL or the estimated rows exceed 1 000, sample up to 100 000 rows to evaluate cardinality. Fields with very high cardinality (e.g., a gender column where one value dominates) are not recommended for indexing.

Analyze GROUP BY and ORDER BY columns using the same algorithm, then merge them with WHERE fields to propose composite indexes.

Check whether the suggested fields already have indexes; if not, the tool prompts to create them, otherwise it remains silent.

Command‑Line Usage

After granting execute permission, the tool can be run as follows:

shell> chmod 755 sql_helper
shell> ./sql_helper -f test.yaml -q 'select * from sbtest1 limit 1;'

Note: test.yaml is a MySQL configuration file.

Parameters and Limitations

The --sample option controls the number of rows sampled; the default is 100 000, but it can be increased to millions for more accurate decisions.

Only SELECT statements are supported, primarily targeting slow‑log queries.

The tool assumes all WHERE conditions are combined with AND. Complex OR conditions may lead to inaccurate composite-index suggestions.

Index recommendations are advisory; final decisions should consider business requirements, data volume, query frequency, and update patterns.

The tool is intended for CentOS 7 environments.

GitHub repository: https://github.com/hcymysql/sql_helper