Looking for help.
We have a table of “child records”. Very simple structure (see below):
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 (
) 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.