Slow MySQL searching from millions record My table looks like this.

id | hash | date


I've an index on hash which is in binary mode.

I have about 3 million rows right now.

But if I query with the following query, it takes about 4 seconds.

select HEX(hash) as hash 
from animal 
where hash in (UNHEX("19788EAB833C289CD7AB"), 
    UNHEX("74FA22042B131511B6AB"), UNHEX("3F716CE5C8E383D2B657"), 
    UNHEX("B2AE511D489990C13AD0"), UNHEX("FF6FD22DF8498BA74572"), 
    UNHEX("3C4E02A76F0EEA51CB9D"), UNHEX("2A40966445E46A9B47BE"), ...)
    ... = around 100 length


What should I do to make it faster?

EDIT:

The result from using EXPLAIN

1 | SIMPLE | animal | range | hash | hash | 10 | NULL | 7 | Using index condition; Using MRR; Using filesort


EDIT 2: 

I've mysql version 5.6.26.

CREATE TABLE `animal` (
 `id` mediumint(9) NOT NULL,
 `hash` binary(10) NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY `hash` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
hash is unique?  If so, change it from KEY to PRIMARY KEY.  That will save some space, but not change the performance much.

It sounds like your innodb_buffer_pool_size is small.  It should be set to about 70% of available RAM.

Let's analyze what is happening.  You have about 100 hashes and a KEY (of any type) on hash.  It will reach into the key about 100 times in 100 different locations.  ("MRR" is involved here, but those details are not very relevant.)  Each lookup of a hash value in the BTree-organized index probably does not find the desired block in the buffer_pool, so it has to hit the disk to get it.  This means about 100 disk hits.  Assuming you have a cheap disk, these hits will take a total of about 1 second.  (Sorry, I can't explain 4 seconds, unless the cache was "cold" and index blocks needed to be fetched, too.)

So, what will a bigger buffer_pool do?  Eventually (as your queries cache more and more of the table), those 100 will be "cache hits" and not have to go to disk.  And the speed will be sub-second.

Your table is probably under 400MB (regardless of whether you take someone's advice on PRIMARY KEY).  You should be able to afford a buffer_pool bigger than that.