I have a table:

value (UNIQUE KEY)

which currently holds over 50 millions rows (and growing fast) and is hit hard:

process 1 queries this table (>200/sec) with a value to get it's id and insert it of not there
process 2 queries this table (>400/sec) with an id to get it's value.

So both processes are hitting the table and the queries are slowing down.
I considered partitioning but when on id the problem arises that process 2 queries need to check all partitions and when on value the same problems comes the other way around.

What would be an optimal table design for this?
Currently I use MyISAM since I get a higher SELECT performance than InnoDB but if someone tells me to change it, no problem.

thx, Martin

  • what is your read/write ratio? Your QPS does not seem that high, what hardware are you using? What type is your field "value"?
    – Niko S P
    Mar 14, 2012 at 22:07

1 Answer 1


You may find what I am about to suggest a little surprising

Add the following indexes

ALTER TABLE mytable ADD INDEX id_value_ndx (id,value);
ALTER TABLE mytable ADD INDEX value_id_ndx (value,id);

This will actually cache id and value together in the MyISAM Key Cache. That way, all queries are in memory only. Queries will not go to the table at all.

I would also propose you make a dedicated 1GB MyISAM Key Cache just for your table

STEP 01: Create a script to load the table into the dedicated key cache

cd /var/lib/mysql
echo "SET GLOBAL mykeycache.key_buffer_size = 1024 * 1024 * 1024;" > init-file.sql
echo "CACHE INDEX mytable IN mykeycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE mykeycache; >> init-file.sql

STEP 02 : Add this to /etc/my.cnf


STEP 03 : service mysql restart

From here on, every restart of mysql will setup this private key cache for your table

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.