MySQL’s SHOW STATUS command has two counters that are often confusing and result in “what does that mean?” questions:
1. Handler_read_rnd
2. Handler_read_rnd_next
As
I understand it, there is some historical context to the choice of
names here, hearkening back to before I was involved with MySQL, way
back when it was a wrapper around ISAM tables — or even earlier, I don’t
know. (Unireg, anyone?) In any case, these two variables deserve a
little explanation.
Both
counters indicate the number of times the corresponding storage engine
API function has been called. In olden times, the storage engine API
was called the handler API, which is why the variables begin with
Handler_.
Handler_read_rnd counts the number of times the handler::rnd_pos() method is called. This method fetches a row from a table based on a “fixed position,” i.e. a random-read. What this actually means varies between storage engines. For MyISAM, position really means a byte offset from the beginning of the file. For InnoDB, it means to read a row based on a primary key value.
Handler_read_rnd_next
is incremented when handler::rnd_next() is called. This is basically a
cursor operation: read the “next” row in the table. The operation advances the cursor position so the next time it’s called, you get the
next row.
The naming convention probably doesn’t make as much
sense today as it did historically. Monty told me that “rnd” meant
“random,” but now I can’t find a reference to that conversation. In any
case, one of the operations is basically a random read (if you think of
tuples as having a natural order, which historically they did), and the
other is just a read-next operation; there’s nothing random about it,
really. I would welcome any corrections from those who know more about
this than I do.
Why would either of these be called? Usually Handler_read_rnd is called when a sort operation gathers a list of tuples and their “position” values, sorts the tuples by some criterion, and then traverses the sorted list, using the position to fetch each one. This is quite likely to result in retrieving rows from random points in the table, although that might not actually result in random IO if the data is all in memory. Handler_read_rnd_next is usually called for each row of a full or partial table scan.
PS: I’m being a bit vague about the sort-and-fetch algorithm; there are really two algorithms, and they are smarter than I’m giving them credit for. But that’s a topic for another blog post.