How a Slow COUNT(*) Query Escaped MySQL’s Slow Log

Problem Phenomenon

A SELECT COUNT(*) query executed in a production environment ran significantly slower than the long_query_time threshold (set to 2 seconds) but failed to appear in the slow query log. Testing confirmed the issue reproducibly omitted slow COUNT(*) statements from logging.

​Key Configuration

‍

Preliminary Analysis

The query’s execution principle involves InnoDB scanning secondary indexes or the primary key to count rows. Given its duration exceeded long_query_time, it should have been logged. However, the slow log remained empty.

Root Cause Analysis

Source Code Investigation

Key functions in sql_class.cc and log.cc govern slow query logging:

1. ​**THD::update_slow_query_status()**:
   • Sets server_status |= SERVER_QUERY_WAS_SLOW if execution time exceeds long_query_time.
2. ​**log_slow_applicable()**:
   • Checks if the query qualifies for logging based on:
     • SERVER_QUERY_WAS_SLOW or warn_no_index (unused index).
     • thd->get_examined_row_count() >= min_examined_row_limit.

​Critical Finding:

• The SELECT COUNT(*) query’s Rows_examined was incorrectly calculated as ​0, failing the min_examined_row_limit=100 check.

Debugging Verification

Using gdb to trace the query execution:

(gdb) p thd->get_examined_row_count()  // Returned 0
(gdb) p thd->variables.min_examined_row_limit  // Confirmed limit at 100

‍

Solution

To log slow COUNT(*) queries:

1. ​Temporarily Disable Row Count Check:
Set min_examined_row_limit = 0 to bypass the row threshold:

SET GLOBAL min_examined_row_limit = 0;
FLUSH LOGS;

‍

2. Long-Term Recommendation:

1. Keep min_examined_row_limit at ​0​ for accurate slow log capture.
2. Use tools like pt-query-digest to analyze historical logs if retention is critical.

Key Insights

• ​InnoDB’s Row Counting Flaw:
  SELECT COUNT(*) erroneously reports Rows_examined=0 due to internal optimizations, conflicting with min_examined_row_limit.
• ​Parameter Interactions:
  • log_queries_not_using_indexes requires min_examined_row_limit compliance.
  • Adjust parameters cautiously to avoid masking legitimate slow queries.

Conclusion

This case highlights a subtle interaction between MySQL’s slow logging mechanics and InnoDB’s row counting logic. While min_examined_row_limit prevents log noise, it can inadvertently exclude genuine slow queries. For critical environments, prioritize log accuracy over thresholds or supplement monitoring with alternative tools.