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
Best Answer
You may find what I am about to suggest a little surprising
Add the following indexes
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
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 !!!