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:
explain select count(comment_count) from wp_posts; +----+-------------+---------------------------+-----------------------------+ | id | select_type | table,type,possible_keys, | Extra | | | | key,key_len,ref,rows | | +----+-------------+---------------------------+-----------------------------+ | 1 | SIMPLE | all NULLs | Select tables optimized away| +----+-------------+---------------------------+-----------------------------+ 1 row in set (0.00 sec)
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.
The post How rows_sent can be more than rows_examined? appeared first on MySQL Performance Blog.