"jfgroff@gmail.com" <jfgroff@gmail.com> wrote:
> Alex and Gordon,
^^^^
Permutation of characters...

> Thanks a lot for your detailed explanations. The situation is a lot
> clearer to me now, especially the trick to perform the range scan on
> the index instead of the full records -- but this index is so big that
> I'm afraid it won't fit in memory either.

Indexes are compressed (simple prefix-compression works well, because
values are ordered anyway). Typically that saves a lot of memory.

> Which settings can I adjust
> to reserve enough memory for my most heavily-used indexes?

For MyISAM there's the key_buffer, caching only indexes. InnoDB uses
the innodb_buffer_pool to cache all kinds of pages. Secondary indexes
use their own pages, the primary key is clustered with the records.
Depending on the table type(s) used, each of those buffers (but not
both) could be set as high as 50-80% of the available memory. The
manual contains some hints on how to tune those parameters.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/