reduce number of innodb pages read by a query In my mariadb I have a large innodb table (600 millions rows). Simple queries on the table using a secondary index perform slowly so I wanted to check the number of innodb pages read to fulfill one of these queries. For that I compared the status variable innodb_pages_read before and after the execution of a query. In average 230 pages are read to fulfill a query that will return 200 rows.

The question is: is it normal that each query must read so much pages ? I have another similar table (but smaller with only 300 millions rows) and it takes on average only 30 pages read to fulfill a query that returns 200 rows. How could I reduce the number of pages read (and thus I/O) ?
Short Answer:  It depends.

Let's start by assuming "simple query on secondary key", leads to one row and you have

INDEX(x)
SELECT * FROM t WHERE x = 123


Then, for 600M rows, and a cold cache (buffer_pool), here is the analysis:


Drill down the BTree for INDEX(x) looking for 123:  about 5 blocks need to be read because the BTree will be about 5 levels deep.  The leaf node will give you the primary key column(s) for the one (as assumed) row with x=123.
Drill down the BTree for the PRIMARY KEY and data:  also about 5 blocks.


Total 10 blocks.

Let's expand that to saying that there are 200 rows with x=123.


5 blocks to get started in the index.
Scan forward in the index, perhaps 2 more blocks.  (Rule of Thumb:  100 rows per block.)
For each of the 200 x=123 index entries, drill down the PK to find a row.
The first one (assuming cold cache) needs to read about 5 blocks (as before).
The other 199 will need fewer than 5 blocks.  For one thing, the root node is already there.  Are the x=123 values randomly scattered?  Or is x, say, a TIMESTAMP that marches in sync with the PK (which might be an AUTO_INCREMENT?)  So, these 199 could average as low as nearly 1.


Total:  ~20 to ~600.

Now let's change to steady state:

If your table will fit in cache, then eventually the number of blocks read will be zero.  But, let's say your 600M rows is too big for that.

Again, I invoke a Rule of Thumb:  "Ignore the drilldown, but not the leaf node".


for 1-row:  2 blocks.
for 200 rows:  3+200 blocks -- 3 in the index; 200 (or fewer, depending on randomness of x versus PK) in the data.


Rick's "Rules of Thumb".

Text/Blob

If there are large text or blob columns and you are fetching them, then "off-block" storage will be hit.  This will add perhaps at least 200 more reads -- depending of number/size/existence of the wide columns in each row.