Php - optimizing multiple counter increments

Question

On a very heavy traffic LAMP server I'm using a memory table to keep track of several data items as counters. It is implemented like this:

$query = "INSERT INTO daily_info_mem SET di_num=1 ,di_type=9, di_date = current_date(), di_sid= $sid_int ,di_name='user_counter' ON DUPLICATE KEY UPDATE di_num=di_num+1";

The index sets unique di_type and date, so if the counter exists for this date then di_type it is incremented, if not a row for this date and data type is created with 0 value.

There are several such queries for each page view. Which means several mysql calls.

Is it possible to optimize this as much as possible into one mysql call that will update several arbitrary counters and still keep the idea of creating the row if needed or increasing the vale if it exists?

Answer 1

I'd recommend re-thinking this type of query entirely. You're hitting your primary database a lot there for something which could be better handled in-memory. Is it absolutely required to update MySQL instantly every time? If you can get it out of MySQL and into memcached you'll see a huge performance bump, and you can always have a script running in the background to collate the data and push it to MySQL regularly (say, every 5 mins) so you don't loose anything.

If it absolutely must be inside MySQL, you might be better off using a HEAP table rather than an InnoDB table. You'll loose your "on duplicate key" but you could always use a stored procedure/function to do the update. And again, you can always have a scheduled query/timed script to "INSERT INTO... SELECT FROM..." to an archive-type table so you don't loose anything.

Or, use a completely different database for storing your stats. MongoDB has upserts and $increments which makes it a very good choice for statistic gathering.