This table currently has about 14,000,000+ records (and growing). On average, each parent "list" has about 20 children records in this table.

99% of the queries into this table, look like:

SELECT wordId FROM list_word_map WHERE listid = 12345

These queries on average take 2 to 3 seconds, which considering the size of the table, maybe an acceptable number, but we're doing a lot of lookups into this table (probably triple the lookups as compared to new records or updates).

Is there a good way (other than sticking the table in RAM, which I will consider if necessary) to speed up these lookup queries? Maybe changing table type to InnoDB (but I'm not sure).

Also, it seems that the same query into the table should take a lot less time due to caching, however, I noticed that running subsequent queries (the same query) takes the same amount of time (i.e. very little to no caching seems to be happening.

Any suggestions? Will be happy to provide additional information if requested.

Comment

If 99% of your queries only need to get wordId, then you should consider changing your index on listId to (listId, wordId). This is called a covering index, and it allows MySQL to answer that query using only the index, removing the need to lookup data rows.

Regarding key cache stuff, you should be checking the following to determine if your key buffer is adequately sized.

SHOW STATUS LIKE 'Key_reads';
SHOW STATUS LIKE 'Key_read_requests';

Key_reads is the number of time MySQL used disk instead of the key buffer, and Key_read_requests is the total number of times a key was needed.

Also, since MyISAM only stores indexes in memory, not data, you want to make sure to leave some memory for the OS filesystem cache, which is responsible for caching the data.

Comment

It basically means for every 600 or so key lookups, one of them will need to read from the disk. That's very good, it usually indicates that your key buffer is large enough.

You can also do

SHOW STATUS LIKE 'Key_blocks_unused';

which will give an idea of how much of the key cache isn't being used at all. I'm not sure what the key cache block size is, but I'd guess it's probably 16K like InnoDB. If the number of unused blocks is fairly large, you can probably reduce the key buffer size. Having a little room for growth is always a good idea, though.

Comment

SHOW STATUS LIKE 'Table_locks_immediate';
SHOW STATUS LIKE 'Table_locks_waited';

That should give you some idea of how often a query must wait to obtain a table lock. If Table_locks_waited is very low compared to Table_locks_immediate, chances are you're not having locking problems. I haven't tried to use MyISAM in a high read-write concurrency setup, so I have no idea how close these two numbers can get before it signals a real problem.