MySQL Optimization: SQL Tuning Steps, Index Usage, and Table Maintenance

This article is the fourth in a MySQL fundamentals series, focusing on SQL optimization, index usage, and table maintenance.

SQL Optimization Steps

When facing a query that needs optimization, several investigation methods are available.

Using show status to understand execution counts

Run the show status command to view server variables and their values. You can filter the output with LIKE or WHERE . The same information can be obtained from the operating system with mysqladmin extended-status .

Two levels of statistics can be requested:

session level – statistics for the current connection

global level – statistics since the server started

If no level is specified, the session level is used by default.

Among the returned variables, those beginning with Com_ record the number of times each statement type is executed, while those starting with Innodb_ give InnoDB‑specific metrics.

Key Com_ variables:

Com_select – number of SELECT statements executed

Com_insert – number of INSERT statements executed (batch inserts count as one)

Com_update – number of UPDATE statements executed

Com_delete – number of DELETE statements executed

Key Innodb_ variables:

Innodb_rows_read – rows returned by SELECT

Innodb_rows_inserted – rows inserted

Innodb_rows_updated – rows updated

Innodb_rows_deleted – rows deleted

Other useful status variables include Connections, Uptime, Slow_queries, and Threads_connected. A comprehensive list can be found at this blog .

Locating Low‑Efficiency SQL

Two common methods are used:

Enable the slow‑query log to record statements whose execution time exceeds long_query_time .

Example my.cnf configuration:

log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2

Check whether the slow‑query log is enabled:

show variables like "%slow%";

Enable it at runtime:

set global slow_query_log='ON';

Because the slow‑query log records only after a statement finishes, SHOW PROCESSLIST (or mysqladmin processlist ) is useful for real‑time monitoring of long‑running queries.

The columns of SHOW PROCESSLIST are explained below:

Id – thread identifier (useful for KILL )

User – the account executing the query

Host – client IP address

Db – database selected for the thread (NULL if none)

Command – type of operation (Query, Sleep, Connect, …)

Time – duration in seconds

State – current state of the statement (see this article )

Info – the SQL text

Analyzing Execution Plans with EXPLAIN

After identifying a slow statement, run EXPLAIN (or DESC ) to see how MySQL will execute it.

explain select * from test1;

Important columns include:

select_type – type of SELECT (SIMPLE, UNION, SUBQUERY, …)

table – table for this row of output

type – join type (system, const, eq_ref, ref, range, index, all)

possible_keys – indexes that could be used

key – index actually used

key_len – length of the used key

rows – estimated rows examined

filtered – percentage of rows filtered by the condition

extra – additional information

Generally, the closer type is to system , the better the performance; all indicates a full table scan.

Indexes

Indexes are the most common and powerful tool for improving query performance. They allow MySQL to locate rows quickly instead of scanning the entire table.

Index Introduction

Using indexes on frequently queried columns can dramatically reduce I/O and CPU consumption.

Index Types

FULLTEXT – global full‑text index (MyISAM only) for CHAR, VARCHAR, TEXT columns.

HASH – key‑value structure, fast for equality lookups, used by MEMORY engine by default.

B‑Tree – balanced tree (B+Tree) used by InnoDB and most engines.

R‑Tree – spatial index for GEOMETRY columns (few engines support it).

Creating and Dropping Indexes

Normal index:

create index normal_index on cxuan003(id);

Drop index:

drop index normal_index on cxuan003;

Unique index, primary key, composite index, and full‑text index are created similarly; examples are shown in the original article.

Index Usage Details

Composite indexes can be used via the leftmost prefix. For example, a composite index on (id, hash) can be used when the query filters on id alone, but not when filtering only on hash .

create index id_hash_index on cxuan005(id,hash);

Explain the query using the prefix:

explain select * from cxuan005 where id = '333';

When the LIKE pattern starts with a wildcard ( % ), the index cannot be used; when the wildcard is at the end ( 1% ), the index is usable.

explain select * from cxuan005 where id like '%1';

explain select * from cxuan005 where id like '1%';

Other cases where indexes are ignored include:

OR conditions where one side lacks an index.

Functions or arithmetic on indexed columns (e.g., id + 111 , CONCAT(id,'111') ).

Leading % in LIKE patterns.

ORDER BY on a column not present in the WHERE clause.

Implicit type conversion that forces a full scan.

Use of IS NOT NULL , <> , or != on indexed columns.

Viewing Index Usage Statistics

The status variable Handler_read_key counts how many times rows are read via an index; a low value may indicate the index is not beneficial. Conversely, a high Handler_read_rnd_next value suggests many full‑table scans and a need for better indexing.

MySQL ANALYZE, CHECK, and OPTIMIZE Table

These commands help maintain table health and performance.

ANALYZE TABLE

Collects distribution statistics for better query planning.

analyze table cxuan005;

CHECK TABLE

Verifies that a table and its indexes are free of corruption.

check table cxuan005;

OPTIMIZE TABLE

Defragments the table and reclaims space after massive deletions or updates. It works for MyISAM and BDB tables; InnoDB tables should be rebuilt and analyzed instead.

optimize table cxuan005;

For further reading, see the recommended articles linked at the end of the original post.