Diagnosing and Optimizing MySQL Query Performance Issues

Recently, the author, after learning to implement PHP service interfaces, encountered severe performance problems with certain MySQL query interfaces, especially when the tables contain large amounts of data or when the same code runs in different environments.

Performance issues when table data volume is large

The interface becomes slow when the underlying table holds many rows.

Performance varies across environments.

Using EXPLAIN reveals how MySQL utilizes indexes for SELECT statements and joins, helping to choose better indexes and write more efficient queries. The output includes ten columns (id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra); the most relevant are key (the actual index used) and rows (the number of rows examined).

Index issues

If the query does not use an index, adding an appropriate one can dramatically speed up retrieval.

Excessive result set size

In one case, a query took 0.2388 s because, despite having an index, it returned a massive amount of data. Two recommendations were given:

Determine whether the full result set is truly needed; add additional WHERE conditions to limit the data returned.

Fetch data in batches, for example by implementing pagination, to reduce the amount processed per request.

After applying these changes, the performance issue was resolved.

Performance differences across environments

Different MySQL connection methods cause varying query latencies. In the test environment the service connects to a local database (negligible network cost), while in production it connects to a remote IP, introducing network delay. Consequently, the same query runs noticeably slower in production, and response time grows linearly with the number of queries.

Recommended troubleshooting steps:

Verify that test and production schemas, especially indexes, are identical.

Measure execution time of individual SQL statements to locate the slow ones.

Use EXPLAIN to check index usage and row counts; add indexes or reduce rows examined where possible.

Refactor code to add WHERE clauses, pagination, or otherwise reduce the total number of SQL statements executed.

The author invites others to share similar MySQL performance challenges and encourages ongoing learning.