Optimize SELECT ... WHERE IN (...)

Question

I'm receiving a sequence of product IDs from external system. I have to show the product information preserving the sequence.

I'm using the following select to do so:

SELECT * FROM  products
WHERE  prodid in (10331,11639,12127..) ORDER BY Field(prodid, 10331,11639,12127...);

Sequence can consist of 20 IDs. prodid has b-tree index.

It's very frequent query, and I'm trying to find ways to improve performance of that part of the system. Now the average time for this query is 0.14-0.2 sec I would like decrease time to 0.01-0.05 sec.

What is the best way to do so? MySQL HASH index, store product id in memcached, or something else?

Answer 1

SELECT * FROM  products                         <<-- select * is non-optimal
WHERE  prodid in (10331,11639,12127..) 
ORDER BY Field(prodid, 10331,11639,12127...);   <<-- your problem is here

First put an index on prodid see @Anthony's answer.

Than change the query to read:

SELECT only,the,fields,you,need FROM  products
WHERE  prodid in (10331,11639,12127..) 
ORDER BY prodid

If you make sure your IN list is sorted ascending before offering it to the IN clause, the order by prodid will yield the same result als order by field(...

• Using a function instead of a field kills any chance of using an index, causing slowness.
• select * will fetch data you may not need, causing extra disk access, and extra memory usage and extra network traffic.
• On InnoDB, if you only select indexed fields, MySQL will never read the table, but only the index saving time (in your case this is probably not an issue though)

What is the best way to do so? MySQL HASH index, store product id in memcached, or something else?

There are a few tricks you can use.

• If the products table is not too big, you can make it a memory table, which is stored in RAM. Don't do this for big tables, it will slow other things down.
  You can only use hash indexes on memory tables.
• If the prodid's are continuous, you can use BETWEEN 1000 AND 1019 instead of
  IN (1000, 1001 ..., 1019)

Answer 2

You may try to create a union of results:

SELECT * FROM  products WHERE prodid = 10331
UNION ALL
SELECT * FROM  products WHERE prodid = 11639
UNION ALL
.
.
UNION ALL
SELECT * FROM  products WHERE prodid = 12127