I have an InnoDB table with just two columns, a VARCHAR(20) and a DATETIME, and around 4M rows. This table serves as a blacklist for other tables, and is truncated and recreated from a csv file from time to time. There are no writes involved besides that, it's only used for a SELECT checking if a key exists, which always hits the index for the VARCHAR column.
The problem is, this table has to be consulted for every single operation in all my systems, all the time, because if there's a match, which is very rare, the operation must be aborted immediately. From my application profiling we spend around 10% of the database time reading from it.
I'm considering using the Memory engine for this table. The idea is to create a base table with the CSV engine that just loads the csv file instead of the whole data importing operation, and a init script to populate the Memory table. I'm assuming the HASH index on the VARCHAR column would be faster for simple lookups, but I'm not sure if it performs well with the almost 100% miss ratio I have.
Is this a good idea to improve my lookup speed?