Is there a way to get rows_examined in MySQL without the slow log?

Question

I'm building some profile information for a home grown app. I'd like the debug page to show the query sent along with how many rows were examined without assuming that slow_log is turned on, let alone parsing it.

Back in 2006, what I wanted was not possible. Is that still true today?

I see Peter Zaitsev has a technique where you:

1. Run FLUSH STATUS;
2. Run the query.
3. Run SHOW STATUS LIKE "Handler%";

and then in the output:

Handler_read_next=42250 means 42250 rows were analyzed during this scan

which sounds like if MySQL is only examining indexes, it should give you the number. But are there a set of status vars you can poll, add up and find out how many rows examined? Any other ideas?

Answer 1

It's slightly better than it was in 2006. You can issue SHOW SESSION STATUS before and after and then look at each of the Handler_read_* counts in order to be able to tell the number of rows examined.

There's really no other way.. While the server protocol has a flag to say if a table scan occurred, it doesn't expose rows_examined. Even tools like MySQL's Query Analyzer have to work by running SHOW SESSION STATUS before/after (although I think it only runs SHOW SESSION STATUS after, since it remembers the previous values).

I know it's not related to your original question, but there are other expensive components to queries besides rows_examined. If you choose to do this via the slow log, you should check out this patch:

http://www.percona.com/docs/wiki/patches:microslow_innodb#changes_to_the_log_format

I can recommend looking for "Disk_tmp_table: Yes" and "Disk_filesort: Yes".

Answer 2

Starting in 5.6.3, the MySQL performance_schema database also exposes statements statistics, in tables such as performance_schema.events_statements_current.

The statistics collected by statements include the 'ROWS_EXAMINED' column.

See http://dev.mysql.com/doc/refman/5.6/en/events-statements-current-table.html

From there, statistics are aggregated to provide summaries.

See http://dev.mysql.com/doc/refman/5.6/en/statement-summary-tables.html