Most Efficient MySQL query to update a table from identical memory table

Question

I'm implementing a memory cache for a table that looks like this (simplified):

Item1 (integer), Item2 (integer), cnt (Integer)

The original table includes millions of pairs like this. and it updates rapidly.

To make it all more efficient I want to write new pairs to an identical memory table and update the real table on disk periodically by cron.

The cron should do the following: for each pair if there is similar pair in the non-memory table increase the count by the count from the memory table. If no such pair exist create it with count from the memory table.

How can I make the flush (from memory table to real table) most efficient?

Notes: The environment is Mysql 5.0.45 PHP 5.2.6 CentOS

Answer 1

You could use a INSERT ... ON DUPLICATE KEY UPDATE query - but that depends on the primary keys or UNIQUE indexes on your master table.

INSERT 
    INTO <<master_table>> (Item1, Item2, cnt)
    SELECT Item1, Item2, cnt FROM <<memory_table>>
    ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

Answer 2

Create a PRIMARY KEY on the disk table:

ALTER TABLE maintable ADD CONSTRAINT pk_maintable_item1_item2 (item1, item2)

and issue the following query:

INSERT
INTO    maintable
SELECT  item1, item2, COUNT(*) AS cnt
FROM    memtable mem
GROUP BY
        item1, item2
ON DUPLICATE KEY
UPDATE  cnt = maintable.cnt + mem.cnt

Note, however, that if you have lots of DISTINCT item1, item2 pairs, then the solution proposed by @S. Gehrig will most probably work better (due to overhead on GROUP BY)