0

In a tb with 1 mil. rows if I do (after I restart the computer - so nothing it's cached):
1. SELECT price,city,state FROM tb1 WHERE zipId=13458;
the result is 23rows in 0.270s

after I run 'LOAD INDEX INTO CACHE tb1' (key_buffer_size=128M and total index size for tb is 82M): 2. SELECT price,city,state FROM tb1 WHERE zipId=24781;
the result is 23rows in 0.252s, Key_reads remains constant, Key_read_requests is incremented with 23

BUT after I load 'zipId' into OS cache, if I run again the query:
2. SELECT price,city,state FROM tb1 WHERE zipId=20548;
the result is 22rows in 0.006s

This it's just a simple example, but I run tens of tests and combinations. But the results are always the same.
I use: MySql with MyISAM, WINDOWS 7 64, and the query_cache is 0;

SHOULDN'T key_cache be faster than OS cache ??
SHOULDN'T be a huge difference in speed, after I load the index into cache ??
(in my test it's almost no difference).

I've read a lot of websites,tutorials and blogs on this matter but none of them really discuss the difference in speed. So, any ideas or links will be greatly appreciated.
Thank you.

Jenny D
  • 27,780
  • 21
  • 75
  • 114
silversky
  • 103
  • 2

1 Answers1

0

It is extremely likely that all the queries use both the myisam key-cache and the OS cache.

MyISAM uses its own cache for indexes, and the OS's one for data files. "LOAD INDEX INTO cache" loads the whole index (if possible), but running the select just loads the required bits, but also loads the data blocks as required.

"LOAD INDEX INTO cache" does not load the data file blocks in, just the index blocks. The index blocks are probably not causing most of the IOs to satisfy the query.

You can check the explain plan using EXPLAIN to see if the query uses a covering index - I expect it will not. If the query does not use a covering index, then some disc IO is required to read the data blocks from the data file, even if you've done a "LOAD INDEX" which only loads the index.

I hope this makes some sense.

Having said that, on such a trivially small table it really doesn't matter.

The OS cache and the keycache are doing different things and are both needed sometimes.

If you have such a small database, consider running it out of a ramdisc if its contents are effectively readonly (i.e you don't need durability)

MarkR
  • 2,928
  • 17
  • 13
  • Meanwhile I've done a lot of reading and now I understand that they are two different things. But your explanation it's very good and descriptive. Thank you. – silversky Dec 03 '10 at 02:01