7

How can the following SELECT WHERE IN case be optimized?

I have a table with over 100 million rows with only 3 columns. The primary key (col1) is a 127 varchar. I am performing a SELECT col1 WHERE col1 IN (...) where the IN clause is 5,000 strings. I am just trying to see which of the 5,000 strings are in the DB as the primary key.

With a dedicated server and InnoDB table, the query takes anywhere from 3 to 10 seconds which is unacceptable. I don't believe 100 million+ rows should be too difficult for MySQL to do a SELECT from, even when selecting 5k rows, but perhaps I am wrong?

What could be done to optimize this? I have read a little about FULLTEXT keys - would those be better since the key is a 127 varchar? Or would some type of JOIN or UNION speed up this query over a large IN clause?

Any help would be appreciated! Thanks!

----EDIT----

SHOW ENGINE INNODB STATUS; | InnoDB | | ===================================== 2014-07-14 10:59:19 2bf5cf25700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 5 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 6664 srv_active, 0 srv_shutdown, 142740 srv_idle srv_master_thread log flush and writes: 149372 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 417120 OS WAIT ARRAY INFO: signal count 449454 Mutex spin waits 323558, rounds 2089912, OS waits 48403 RW-shared spins 49101, rounds 462555, OS waits 12976 RW-excl spins 406820, rounds 11261153, OS waits 350839 Spin rounds per wait: 6.46 mutex, 9.42 RW-shared, 27.68 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 21503 Purge done for trx's n:o < 21472 undo n:o < 0 state: running but idle History list length 641 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 15895, OS thread handle 0x2bf5cf25700, query id 399305 localhost root init SHOW ENGINE INNODB STATUS -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1203330 OS file reads, 2141172 OS file writes, 78570 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 52 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 117811837521 Log flushed up to 117811837521 Pages flushed up to 117811837521 Last checkpoint at 117811837521 0 pending log writes, 0 pending chkp writes 21279 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 720503 Buffer pool size 8191 Free buffers 1024 Database pages 7115 Old database pages 2606 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4741, not young 1045985243 0.00 youngs/s, 0.00 non-youngs/s Pages read 1202370, created 1138775, written 2068616 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 7115, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 23063, id 3020409116416, state: sleeping Number of rows inserted 113995729, updated 144489445, deleted 0, read 171054938 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Improve this question
1
  • Can you add the SHOW CREATET ABLE tablename; output? Commented Jul 14, 2014 at 16:49

2 Answers 2

Reset to default
3

I have had success in general replacing lengthy IN operations with a join against a temporary table. This makes sense because RDBMS are optimized to perform JOIN's as efficiently as possible, and the handling of lengthy IN lists will most likely be done by repeating the query for every value in the IN list. The performance issue you see could easily be explained by the simple fact of the query being run 5,000 times.

Improve this answer
1
  • 1
    Just benchmarked this on MariaDB 10.5 with a WHERE IN() containing 30,000 values, vs insert 30,000 values in a temporary table (type=MEMORY) + JOIN. The result: insert+join is 70x faster than WHERE IN. Note that all the inserts are done in a single query with extended inserts. Commented Mar 20, 2023 at 11:20
1

The following is a long shot, as we do not know anything about your hardware, InnoDB configuration, and query specifics, but I bet you are using the wrong tool for the job (InnoDB Engine).

What you are trying to achieve is creating a very heavy index (up to 127 characters, which may take -this is a broad approximation- 127*3 bytes per entry), which is created using the only method available for InnoDB, a B+Tree. Also, as rows are clusterized around the primary key, the whole row is actually on the index, and accessing the primary key means accessing the page with the whole row content.

In short, you have a unique index, which contains your whole table, and which should fit more or less on memory (not necessarily all, but in this case your working set seems to be most of your the rows). How big is your InnoDB buffer pool? How is your buffer pool hit ratio? You can check both parameters with SHOW ENGINE INNODB STATUS. My bet is that your buffer pool is too small or even that your you do not have enough physical memory to hold your working set. In both cases, this may be forcing InnoDB to perform IOPS for every query. You may think that you should not need to have everything cache for everything to work well, and you should be right. But for your particular workload (large PKs), InnoDB is not the best engine. A hash index, available in other RDBMS and MySQL engines, would probably be smaller and faster, but it is not supported by InnoDB. Additionally, IN + list of values with a huge number of rows may not be the most optimal way of querying (at MySQL level), but it should be certainly faster than doing the queries individually.

  • Make sure that the query planner (you can check it with EXPLAIN) is using the range JOIN type, and not doing full table scans.
  • After that, the first thing I would recommend you to tune your InnoDB buffer to reduce InnoDB cache misses.
  • The next thing to try is to emulate a hash index by creating a small secondary index. This method is explained in the book "High Performance MySQL". This way, only the small secondary index would be cached on memory and it may better fit your physical memory.
  • If these do not work, before changing technology, I would recommend you to try to use a different engine with works well with key-value datasets. Maybe TokuDB could be better to handle this? Also, the memcached interface integrated into MySQL/InnoDB 5.6 could be another solution? Multi-get seems to fit your solution very well.
  • At last, if your load is mostly reads, you could try external technology, like full text search engines -as you mention-, but be careful as those kind of pieces of software tend to rely on fuzzy search, and may omit some results and they tend to not be fully ACID compliant (those are things that they have to sacrifice in exchange for query speed).
Improve this answer
7
  • The index is not the whole table, there are 2 other columns. Anyway, I edited the question to add the INNODB Engine Status. Please review and advise more specifically if you are able. Commented Jul 14, 2014 at 15:28
  • The command shows no activity, probably because it has been execute only once. Try this instead: SHOW GLOBAL STATUS LIKE 'InnoDB%'; before and after executing several of your queries. Please add also the result of EXPLAIN SELECT + query, as suggested above. Commented Jul 15, 2014 at 10:09
  • I can already tell you that you have a 128MB buffer pool, which may be too small for you dataset. The typical size for a dedicated server is to cover the whole "working set" (frequently used rows) + 10%, up to the 80% of the available memory. Commented Jul 15, 2014 at 10:42
  • I have 8 GB of RAM, so would you recommend setting the buffer pool to, say, 5 or 6 GB? Commented Jul 15, 2014 at 13:43
  • Those are reasonable defaults, assuming you are only (mostly) using InnoDB and your data size is larger. Please note that MySQL should at any time have 0 swap activity, so make sure you monitor it after change it. Commented Jul 15, 2014 at 17:17

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.