optimal table design mysql with primay key and varchar value

Question

I have a table:

id (PRIMARY KEY AUTO_INCREMENT)
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

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

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

[mysqld]
init-file=/var/lib/mysql/init-file/sql

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 !!!