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 (....)

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.

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.