MySQL Forums
Forum List  »  Performance

How to get Rows_examined WITHOUT using slow query log
Posted by: c k
Date: December 02, 2009 01:17AM

When you are optimizing a query it is desired that you can try different variations on the query and immediately be able to see who they perform.

Rows_examined is one of the important parameter to watch while optimizing a query.

As of now, only way I know to get this value is to look into slow query log.
That is inconvenient, at times too painful.

If you are optimizing a query on a busy production server, your query does not appear in slow query log, you either do not have rights to change value of long_query_time or simply can not afford to reduce its value, then how do you find Rows_examined?

Two suggestions are found in forums, but both do not work well:
1) flush status / show status - Not workable as you either do not have rights to flush gloabl status or can not afford to do so as it will disturb other monitoring tools
2) show session status where Variable_name like 'handler%' - On my local system, none of the handler% variable values matched with Rows_examined in slow query log.
e.g. for query where slow query log shows Rows_examined=121710, i get

mysql> show session status where Variable_name like 'handler%next';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_next | 31038 |
| Handler_read_rnd_next | 10508 |
+-----------------------+-------+

Any simple way to get the value Rows_examined?

Regards
CK

Options: ReplyQuote


Subject
Views
Written By
Posted
How to get Rows_examined WITHOUT using slow query log
2930
c k
December 02, 2009 01:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.