Understanding MySQL Slow Query Logs and Index Optimization for QA Engineers

As a QA professional, you often encounter slow API responses or concurrency errors during performance testing, and investigating the root cause—especially database bottlenecks—can boost both collaboration with developers and personal expertise.

MySQL provides performance analysis tools such as the slow query log. Unlike Oracle's built‑in AWR, MySQL requires you to enable and configure the slow query log manually.

Configuration can be done via the /etc/my.cnf file or directly with SQL commands. The key parameters are:

slow_query_log : enable (1) or disable (0) the slow query log.

slow_query_log_file : path to the log file.

long_query_time : threshold in seconds (default 10 s) for a query to be considered slow.

Common command‑line operations (run as an administrator) include: set global log_slow_queries = on; – enable the slow query log. show variables like 'long_query_time'; – view the current threshold. set global long_query_time = 1; – lower the threshold to 1 s (effective immediately, but resets after a server restart). show variables like 'slow_query_log_file'; – display the log file location. set global log_output='TABLE'; – store slow query logs in a table instead of a file (generally not recommended for production due to performance impact).

After enabling the log, you can simulate a slow query with SELECT SLEEP(10); and then inspect the recorded entry in the log table.

When a slow query is identified, optimizing it often starts with proper indexing. Key indexing principles for QA to understand include:

Use unique indexes for columns with unique values (e.g., student ID).

Create indexes on columns frequently used for ORDER BY, GROUP BY, DISTINCT, or UNION.

Index columns that appear often in WHERE clauses.

Limit the number of indexes to reduce storage and maintenance overhead.

Prefer high‑cardinality columns (distinct/total ratio > 0.1) for effective indexing.

Extend existing indexes instead of creating new ones when possible.

Additional practical rules:

Follow the left‑most prefix rule; MySQL can use an index only up to the first range condition.

Avoid functions on indexed columns (e.g., FROM_UNIXTIME(create_time)) because they prevent index usage.

Use short (prefix) indexes for long string columns when the prefix is sufficiently selective.

Remember that MySQL uses only one index per query; design composite indexes accordingly.

Patterns like LIKE 'aaa%' can use indexes, but LIKE '%aaa%' cannot.

Minimize OR conditions unless each operand has its own index.

Avoid implicit type conversions in predicates.

Let MySQL decide to skip indexes when a full table scan is cheaper.

Avoid !=, <>, and IS NULL checks on indexed columns when possible.

For deeper query analysis, the EXPLAIN statement is a powerful, easy‑to‑use tool that reveals how MySQL executes a query; future articles will cover its usage in detail.

In summary, by enabling slow query logging, reviewing the captured statements, and applying solid indexing guidelines, QA engineers can proactively detect and help resolve database performance issues, improving overall system reliability.