Why are almost all ROWS_EXAMINED values 0 in events_statements_* tables (performance_schema) !?
In the last days I started to explore the new possibilities of the perofmance_schema in 10.0.x. In this process I discovered the valuable event_statemens_* tables, which I could imagine using for some new monitoring approaches.
While studying the content of the event_statemens_* on our production DB server, I have noticed, that almost all ROWS_EXAMINED values were always equal 0! Only on one server, in the events_statements_summary_by_digest table could I find a few entries with ROWS_EXAMINED > 0 (but in those cases, both DIGEST and DIGEST_TEXT were NULL ???) .... which is a real pity, because this value seems to be the best one to be used as some kind of "load" indicator (and it can show very quickly, if a query needs an unreasonable amount of rows to be examined ... etc. ..).
Now the questions is, why is this happening? As far as I could see, all other values in the event_statemens_* tables seemed to have reasonable values. Only ROWS_EXAMINED was missing notoriously. Is there special reason for this? Is there some instrumentation setting needed to make this work? Is there some server variable which influences this? or is there some very special technical reason - from the servers logic - which prohibits to gather this value most of the time?
By the way, I have also observed - and this might coincide with this issue - that neither do I ever see EXAMINED_ROWS > 0 in `information_schema`.`PROCESSLIST`! Even if I fire a lung running query, which uses a table scan.
By the way, the only place where I always - and reliably - see ROWS_EXAMINED is in the slow_query_log!
I am really curious about the background (and possible reasons) for this - seemingly - strange behavior.
Thank you very much!
PS: we are - currently - running MariaDB 10.0.14 (on Ubuntu 12.04) in our production environment. But I could observe the same behavior on one - already updated - 10.0.16 dev machine.
Answer
Because number of examined rows is accumulated locally per JOIN object (basically — per SELECT) and at the end of the execution they're all added up, so in many cases ROWS_EXAMINED only becomes non-zero at the end of the query.
But not always. If you try SELECT with subqueries, you might see non-zero ROWS_EXAMINED when a subquery was already executed but top-level SELECT is still in progress. If you have a dependent subquery (that will be executed many times witin you top-level SELECT), you might see how ROWS_EXAMINED is gradually increases.
Perhaps you can also see non-zero ROWS_EXAMINED with UNION.
