reduce number of innodb pages read by a queryPosted: asked May 19 at 6:46 - Source : stackoverflow
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) ?