Check if buffer was used in last query

Question

I was wondering whether there is any query/config/trick/etc to know if the innodb_buffer was used in the fetching of result for last query.

PS: This is in context of performance tuning, and I don't want to keep things to best guesses, so is there any way to provide a concrete evidence if buffer_pool was used or a normal db lookup was used.

PPS: I already searched for related terms like

• check if buffer was used mysql
• Innodb buffer used check
• Verify if results loaded from buffer pool or datastore. etc..

Answer 1

Watch for changes to GLOBAL STATUS values of Innodb%. Ditto for Handler%.

I like to do this for figuring out what is going on in a query:

 FLUSH STATUS;
 SELECT ...
 SHOW SESSION STATUS LIKE 'Handler%';

It tells me exactly how many rows are touched how many times. And whether a temp table is being used and how big it is (in rows). Etc.

The only way the buffer_pool won't be "used" in an InnoDB query is if the Query cache is used instead.

Probably what you are fishing for is not whether the buffer_pool is "used", but whether a block had to be fetched from disk before it could be used?