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
Subject
Views
Written By
Posted
How to get Rows_examined WITHOUT using slow query log
2930
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.