How can I select a set of IDs from large table fast?

Question

I have a large table with ID as primary. About 3 million rows and I need to extract a small set of rows base on given ID list.

Currently I am doing it on where... in but it's very slow, like 5 to 10s.

My code:

select id,fa,fb,fc 
from db1.t1 
where id in(15,213,156,321566,13,165,416,132163,6514361,... );

I tried to query one ID at a time but it is still slow. like

select id,fa,fb,fc from db1.t1 where id =25;

I also tried to use a temp table and insert the ID list and call Join. But no improvement.

select id,fa,fb,fc from db1.t1 inner join  db1.temp  on t1.id=temp.id

Is there any way to make it faster?

here is table.

CREATE TABLE  `db1`.`t1` (
  `id` int(9) NOT NULL,
  `url` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastModified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Ok here is explain select.

id=1,
select_type='SIMPLE', 
table='t1', 
type='range', 
possible_keys='PRIMARY', 
key='PRIMARY',
key_len= '4',
ref= '', 
rows=9, 
extra='Using where'

Hm, PK should always be indexed but from your description sounds like not. What indexes do you have? — Brad Peabody, Oct 03 '13 at 01:05
It have ID as primay key. I think it mean it is indexed. correct me if i am wrong. — user1748365, Oct 03 '13 at 01:07
Also run `EXPLAIN ` and post results in your question in a textual form — peterm, Oct 03 '13 at 01:11
It doesn't add up there is no `urlId` column in your `CREATE` statement that you mentioned in `PRIMARY KEY (urlId)` — peterm, Oct 03 '13 at 01:13
The table is quite big. Just conversion could take hours. It is about 60gb now. I hope there is other choice. else I will try test out tonight. — user1748365, Oct 03 '13 at 01:24
What does `SELECT @@key_buffer_size;` say? Is your table actively inserted into/updated while you select from it? — peterm, Oct 03 '13 at 01:54

score 0 · Answer 1 · edited May 23 '17 at 12:28

Here are some tips how you can speed up the performance of your table:

Try to avoid complex SELECT queries on MyISAM tables that are updated frequently, to avoid problems with table locking that occur due to contention between readers and writers.
To sort an index and data according to an index, use myisamchk --sort-index --sort-records=1 (assuming that you want to sort on index 1). This is a good way to make queries faster if you have a unique index from which you want to read all rows in order according to the index. The first time you sort a large table this way, it may take a long time.
For MyISAM tables that change frequently, try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.
Strings are automatically prefix- and end-space compressed in MyISAM indexes. See “CREATE INDEX Syntax”.
You can increase performance by caching queries or answers in your application and then executing many inserts or updates together. Locking the table during this operation ensures that the index cache is only flushed once after all updates. You can also take advantage of MySQL's query cache to achieve similar results; see “The MySQL Query Cache”..

You can read further on this articles on Optimizing your queries.

score 0 · Answer 2 · answered Nov 27 '13 at 03:31

First of all clustered indexes are faster then non-clustered indexes if I am not wrong. Then sometime even you have index on a table, try to create re-index, or create statistics to rebuild it.

I saw on SQL explain plan that when we use where ID in (...), it converts it to Where (ID =1) or (ID=2) or (Id=3)..... so bigger the list many ors, so for very big tables avoid IN ()

Try "Explain" this SQL and it can tell you where is the actual bottle neck. Check this link http://dev.mysql.com/doc/refman/5.5/en/explain.html hope will work

score -1 · Answer 3 · answered Oct 03 '13 at 01:40

-1

Looks like original sql statement using 'in' should be fine since the Id columns is indexed

I think you basically need a faster computer - are you doing this query on shared hosting?

answered Oct 03 '13 at 01:40

Super Nerd

11
2

well it is i7 with 16gb ram on the SSD. Actually I got this on development box and also testing on vps. VPS seem to be slightly better some how. – user1748365 Oct 03 '13 at 06:08

How can I select a set of IDs from large table fast?

3 Answers3