Caching only index file

MYI extension is the signature to refer to MySQL MyISAM index file. We are going to cache only this index file and test the system performance.

# reboot

# free
total used free shared buffers cached
Mem: 255504 50212 205292 0 6040 26976
-/+ buffers/cache: 17196 238308
Swap: 0 0 0

# cat /var/lib/mysql/full_text_investigations/*.MYI > /dev/null

# free
total used free shared buffers cached
Mem: 255504 135852 119652 0 6176 109716
-/+ buffers/cache: 19960 235544
Swap: 0 0 0

Filesystem cache has read MYI file and has grown from 27 Mb to 110 Mb in size. Remember that MYI file occupies 81 Mb. Size of the cache and the file cached match. That's good. Now we query:

65 rows in set (1.51 sec)
165 rows in set (2.99 sec)
256 rows in set (3.51 sec)
27 rows in set (0.51 sec)
5 rows in set (0.08 sec)
Empty set (0.00 sec)
28 rows in set (0.51 sec)
236 rows in set (3.37 sec)
30 rows in set (0.55 sec)
75 rows in set (1.25 sec)

Oh! What the hell! Slow responses! Very similar timing to the obtained when not caching at all. Is that meaning that caching the index file is useless? The answer is YES, at least in this kind of "full-text search engine" queries.

Index file seems not to be the key factor to improve performance. Let's do next the same experiment but with the data file.