>>>>> "chas" == chas <panda@stripped> writes:
>> Generally the smaller you can make your tables, the faster the queries
>> go. The trick to table design is to fit the entire table or a large
>> portion of it into RAM. RAM somewhere in the order of 1000 faster to
>> access than disk, so the difference between having your data entirely in
>> RAM and only partially, the other part being on the disk is someting the
>> difference between going to a store 5 minutes away to buy milk and
>> driving to New York from Calfornia to accomplish the same.
chas> Thank you Sasha,
chas> This raises a topic that I've been very interested in - keeping entire
chas> tables in memory as well as caching queries. This seems to be common practice
chas> by the big search engines (with GB of RAM). I've scoured section 10.1
chas> of the MySQL reference manual several times, looking for the exact syntax
chas> for this but I can't seem to find it. I had suspected that it was the
chas> settings but in a previous post, Monty said :
chas> Everything depends on the queries. The record buffer is only used when
chas> MySQL is forced to read sequentially through the hole table.
chas> You will normally not get any more performance using a record buffer of
chas> more than 1M or a sort_buffer of more then 8M.
chas> If the queries are slow, it normally means that MySQL can't find a
chas> suitable index for your queries.
chas> Basically, how does one say to MySQL "always store table X in memory"
chas> and how can we check that it is actually doing this ?
chas> in fact, is there any way to check what MySQL is actually doing (holding
chas> in memory) at any one instant in time ?
MySQL lets the OS cache the rows while MySQL only caches the key usage.
This makes it impossible to determinate what is cached and what isn't.
MySQL 3.23 supports HEAP (in memory) tables, which will are very fast,
but as these are not saved on disk they are mostly suitable as