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?