mysql rows examined_有没有办法在没有慢速日志的情况下在MySQL中获取rows_examined？...

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:

Run FLUSH STATUS;

Run the query.

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?

解决方案

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:

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