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.
Best Answer
Are there multiple rows for each
dna_bits
? If not, then that column could be thePRIMARY 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.