1

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.

  • what is in the "in (...)" clause? In clauses can be very inefficient. Since you are getting 10000 of them, I suspect you have a subquery in the in clause which will absolutely kill your query. – user67871 Dec 2 '15 at 20:12
  • It's just pure bits query. Where (b'010101', b'0101000', ...) – moeseth Dec 2 '15 at 20:14
  • You actually listed 10000 hardcoded items in your query, or did you use a subquery to get that list from another table? – user67871 Dec 2 '15 at 20:15
  • hard coded. I generated those values before querying. – moeseth Dec 2 '15 at 20:16
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.