# index query on table with 14mm+ (and growing) records takes more than 2 seconds **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [August 18, 2009, 8:34pm UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240 "2009-08-18T20:34:44Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [August 18, 2009, 8:34pm UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/1 "2009-08-18T20:34:44Z")
Hi All, Looking for help. 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. Thanks ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 19, 2009, 10:12am UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/2 "2009-08-19T10:12:50Z")
Does the key fit in key\_cache? ---
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [August 19, 2009, 11:10am UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/3 "2009-08-19T11:10:56Z")
Where / how do I detect / measure that? ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 19, 2009, 2:41pm UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/4 "2009-08-19T14:41:19Z")
Find the size of all frequently used indices and check the ini setting for key\_buffer\_size. ---
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [August 19, 2009, 3:07pm UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/5 "2009-08-19T15:07:15Z")
If you don’t mind, how do I find the size of all frequently used indices? Also, key\_buffer would be a setting in my mysql configuration file? thanks ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 19, 2009, 3:49pm UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/6 "2009-08-19T15:49:08Z")
Yup, ini setting. Take the sum of the \*.MYI files of your frequently queried tables. Or just the sum of all MYI files, if memory allows. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 20, 2009, 5:14am UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/7 "2009-08-20T05:14:01Z")
Have you ever checked whether you have locking issues? Check the process list for threads in locked mode. ---
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [August 20, 2009, 11:51am UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/8 "2009-08-20T11:51:07Z")
So I added up all of my .MYI (thanks for the recommendation) and turns out that they are around 621M. I’ve set my key\_buffer size to 2000M (just to give it some room), but maybe 1000M is sufficient? What do you think? Thanks ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 20, 2009, 3:43pm UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/9 "2009-08-20T15:43:01Z")
| [B]gmouse wrote on Thu, 20 August 2009 12:44[/B] | | Have you ever checked whether you have locking issues? Check the process list for threads in locked mode. | ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 21, 2009, 3:39am UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/10 "2009-08-21T03:39:08Z")
It is hard to imagine that with queries taking 2-3 seconds you do not have any locking issues. You could try [URL][http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html[/URL]](http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html%5B/URL%5D) for checking how those 2-3 seconds are split among several tasks. ---
**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\ **Post date:** [September 7, 2009, 11:44pm UTC](https://forums.percona.com/t/index-query-on-table-with-14mm-and-growing-records-takes-more-than-2-seconds/1240/11 "2009-09-07T23:44:13Z")
Yes, what is your server actually doing? 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? etc. Good Luck! )