Analyzing MySQL Slow Query Log Source Code and Debugging SELECT COUNT(*) Issues

In a production environment the statement select count(*) from table runs very slowly, exceeding the long_query_time threshold, yet it is not written to the slow‑query log, a problem that can also be reproduced in a test environment.

The relevant slow‑query parameters were set as follows:

slow_query_log = 1                     # enable slow query log
slow_query_log_file = /mydata/3306/log/mysql.slow.log  # log file path
log_queries_not_using_indexes = 1    # log queries without index usage
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
long_query_time = 2                  # threshold in seconds
min_examined_row_limit = 100        # queries examining fewer rows are ignored

The execution principle of select count(*) is that InnoDB traverses the secondary index or primary key and counts rows row‑by‑row, so any query exceeding long_query_time should be marked as slow.

MySQL records a slow query when the server status flag SERVER_QUERY_WAS_SLOW (or the warn_no_index condition) is set and the number of examined rows is greater than or equal to min_examined_row_limit . The source code responsible for this logic includes:

void THD::update_slow_query_status() {
  if (my_micro_time() > start_utime + variables.long_query_time)
    server_status |= SERVER_QUERY_WAS_SLOW;
}

bool log_slow_applicable(THD *thd) {
  bool warn_no_index = ((thd->server_status & (SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED)) &&
                        opt_log_queries_not_using_indexes &&
                        !(sql_command_flags[thd->lex->sql_command] & CF_STATUS_COMMAND));
  bool log_this_query = ((thd->server_status & SERVER_QUERY_WAS_SLOW) || warn_no_index) &&
                        (thd->get_examined_row_count() >= thd->variables.min_examined_row_limit);
  if (log_this_query) thd->status_var.long_query_count++;
  if (thd->enable_slow_log && opt_slow_log) {
    bool suppress_logging = log_throttle_qni.log(thd, warn_no_index);
    if (!suppress_logging && log_this_query) return true;
  }
  return false;
}

Debugging in the MySQL source with gdb (setting breakpoints at THD::update_slow_query_status and log_slow_applicable ) revealed that when the breakpoint in update_slow_query_status is hit, the query’s execution time already exceeds long_query_time and the server status flag is set correctly.

However, stepping into log_slow_applicable showed that thd->get_examined_row_count() returned 0, which is lower than the configured min_examined_row_limit of 100, causing the query to be skipped from logging. The stack trace confirmed this condition.

By setting min_examined_row_limit to 0 (its default) and re‑executing the select count(*) statement, the query is recorded in the slow‑query log, showing a Rows_examined value of 0 and a Rows_sent value of 1.

The author filed a bug (MySQL Bug #110804) because InnoDB should not report Rows_examined as 0 for a full‑table count; the MySQL team confirmed the issue.

In conclusion, although many modern deployments rely on external monitoring tools, the built‑in slow‑query log remains a valuable method for diagnosing slow SQL statements, especially when combined with tools like pt-query-digest for further analysis.