November 26, 2014

How rows_sent can be more than rows_examined?

When looking at queries that are candidates for optimization I often recommend that people look at rows_sent and rows_examined values as available in the slow query log (as well as some other places). If rows_examined is by far larger than rows_sent, say 100 larger, then the query is a great candidate for optimization. Optimization could be as simple as adding a few indexes or much more complicated as in generating summary tables so you do not need to rely on large aggregations for your real-time queries.

Just to be clear this is a great rule for your “real time” queries need to be handled very quickly and in high volumes. Batch jobs, reporting queries often will have to scan through millions of rows to get few rows of result set and it is fine.

So it is all clear with rows_sent being smaller than rows_examined but can it be smaller ? Yes it can! Here are couple of things you need to be aware in this regard:

Query served without Execution
As strange as this might sound it is very much possible in MySQL. Some queries can be completely resolved during the “optimization” stage so there is no need to really execute a query by accessing the tables. You will see “Select tables optimized away” in the query EXPLAIN in this case:

Typically you will see this when having count(*) with MyISAM tables as in the example above as well as using MIN/MAX aggregate functions on the indexed column.

Reading data from the Index
Observing such behavior I’ve seen people thinking it could be due to using “Covering Index.” MySQL is reading data from the index in this case, not rows, right? Wrong! For the sake of counting this variable MySQL does not make a difference whenever it could read the data from the index alone or “data” had to be consulted. In both cases it will be seen as row which has been examined.

Index Condition Pushdown
Index Condition Pushdown really breaks things here as when index entries are filtered on storage engine level they are not counted towards row_examined, at least in MySQL 5.6. This is unfortunate as it may make queries using Index Condition Pushdown to look less expensive than they actually are. In some cases possibly 100x or more less expensive when a Index Condition Pushdown is used to filter out large portion of the rows. I wish storage engine could track how many rows it examines through Index Condition Pushdown and how many it filters out so rows_examined could account it properly.

Query Cache
Query Cache is another interesting case as any query cache hit will have rows_examined and rows_sent being zero. It might be confusing at first – one could expect the rows_examined be zero as no tables are being read while rows_sent have an actual number because result set is sent to the client. What happens in reality is – Query Cache caches the result set packets as they are sent to the client and they are sent again on query cache hit without any parsing so server does not know how many rows are being sent. I wish number of rows sent would be stored together with query result and as such be available in the log files, though I do not expect it ever fixed as Query Cache is something which is not getting a lot of attention this way.

Summary
Well… you can get rows_examined less than rows_sent but I only expect to see it for some rare kind of queries. There are also different cases you need to be aware of which may cause rows_sent or rows_examined not to be represented of the actual processing query requires or size of actual result sets.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Speak Your Mind

*