Explain about MRR?

Question

MySQL

I optimize the site. Table 80 thousand records.
Here is a request that takes more than 500ms.:

SELECT * FROM `posts`
WHERE `category` = "2"  OR `category` = "3"  OR `category` = "6"  OR `category` = "7"  OR `category` = "8"  OR `category` = "9" OR `category` = "11"  OR `category` = "45"
LIMIT 0, 100;

I check with EXPLAIN, MySQL returns:

+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | posts    | NULL       | range | category      | category | 1       | NULL | 13516 |   100.00 | Using index condition; Using MRR |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+

I read about MRR here: dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html
But, either my English fails, or I just don’t understand anything.
Can someone explain clearly? If possible with an example, such as so good and so bad.

Answer 1

InnoDB secondary indexes, as you know, refer to the value of the primary key, and physically on the disks, the data lies next to the primary key and is sorted by this very primary key. So, if the index says that you need id 9, 6, 50, 8 and 7, it is more profitable to read them from disk not in that order, but to re-sort and read them in two queries: 6-9 and 50. So we get 2 random read requests instead of 5. This is very useful for HDDs with slow mechanics, but it also brings its own, albeit more modest, dividends for SSDs - although they are orders of magnitude faster than HDDs in random reading, they are also more comfortable with sequential rather than random reading.
This is exactly what MRR does. First, it gets a list of necessary keys from the index, sorts this list, and queries the disk not for one entry in random places, but in larger consecutive blocks.
I note that MRR comes into play if you need to read a lot of things from the disk. Those. cold read, the data for this sample is mostly not in memory. Of course, this is far from fast.
And one more thing: you don't have an order by in your request, but there is a limit - this means that you don't care which rows to select. Mysql will then return any matching rows in any order it sees fit.