We have a table of “child records”. Very simple structure (see below):
CREATE TABLE list_word_map ( id int(20) unsigned NOT NULL auto_increment, listId int(11) unsigned NOT NULL default ‘0’, wordId int(20) unsigned NOT NULL default ‘0’,
PRIMARY KEY (id),
KEY listid (listId)
) ENGINE=MyISAM AUTO_INCREMENT=28599140 DEFAULT CHARSET=latin1
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.
If I run a test on my laptop with 15m+ records with your table design and the average 20 rows per listid.
I can do the first query in 0.27 seconds (before the index is load into key_cache) and consecutive queries in the <0.1 seconds time span.
And that is with just 8MB key_cache.
So I think you need to start by looking at what the server is actually doing instead of looking at what MySQL is doing.
Is it swapping?
Do you have high IO wait?