Slow MySQL InnoDB performance of table with 100 million rows

Question

I have the database in this format.

CREATE TABLE `dna` (
 `p_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `dna_bits` bit(32) NOT NULL,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`p_id`),
 KEY `dna_bits` (`dna_bits`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Currently, I have 100 millions rows and I need to query quickly by dna_bits which I have an index on.

My query looks like

select CONV(dna_bits, 16, 2) as bits, user_id from dna where dna_bits in (b'010101', b'0101011', ...)

Searching 10000 dna_bits in where clause took me over 8 seconds.

EXPLAIN on my shorten* query showed this.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  dna range   dna_bits    dna_bits    4   NULL    77  Using where

I'm running MySQL server on my iMac 3.2 GHz Intel Core i5 with 16GB RAM.

What can I do to speed up the process?

Thanks.

Answer 1

Are there multiple rows for each dna_bits? If not, then that column could be the PRIMARY KEY? If you can do that, then the speed might double because you won't be bouncing between the secondary index and the PK. Getting rid of p_id and the secondary key would save space.

How much RAM? How big is the table? (I would guess 5GB data+index.) What is the value of innodb_buffer_pool_size? If that can be set to 70% of available RAM and still be bigger than the table size, then (eventually) all the data you need will be cached. Again a speedup. (Can't tell how much.) This is because of avoiding I/O.

10K lookups will take some time.