Re: How to see key_buffer in action

[Rick James]

You are using MyISAM?

Other techniques...
SHOW GLOBAL STATUS LIKE 'Key%';
SHOW GLOBAL STATUS LIKE 'Uptime';
Then compute the "per second values" to see how much the key_buffer needs to be read/written.
To see the effectiveness, compute things like
Key_reads / Key_read_requests

Another technique for getting insight into what is happening... Do this before and after your query, then find the change in values:
SHOW SESSION STATUS LIKE "Handler%";

WHERE title LIKE "Abkommen%"
will use any index starting with title. And it will use it very efficiently:
1. drill down the BTree (in the .MYI file) to the first key at or after "Abkommen";
2. scan forward from there.
2. For each index entry, it will reach over into the data -- this will be a random fetch from the .MYD file.

How many rows is it fetching? Perhaps not very many, so perhaps only a tiny portion of the key_buffer is really needed.

I do find your timings to be curious -- either ~0.65ms or twice that (~1.3ms), as if there is something discrete in the timer.

If you were to randomly hit all the rows in the table, not just the Abkommen ones, then key_buffer_size = 90M (or a little more) would be advised, based on
> The Index size is 90MB

Your test exercises only a small portion of that, hence
* is not realistic, and
* does not need nearly that big a key_buffer.

(The general advice these days is to switch from MyISAM to InnoDB.)