performance - 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".