I have one big table foobar describing a many-to-many-relation and containing millions of foo's, millions of bar's and every bar having several hundereds of foo's -> billions of rows.

CREATE TABLE `foobar` (
PRIMARY KEY (`foo_id`, `bar_id`),
INDEX `bar_id_idx` (`bar_id`))

I have another table counting the foo_id's in foobar:

CREATE TABLE `foo_amount` (
PRIMARY KEY (`foo_id`),
INDEX `amount_idx` (`amount`))

The counting could be done like this:

INSERT INTO foo_amount (SELECT foo_id, COUNT(*) AS amount FROM foobar GROUP BY foo_id);

But I would have to recompute the table with every inserted/deleted row in foobar.

An insert usually adds a new bar-object with several hundered foo's. For example inserting a bar with bar_id 42 having foo's with foo_id's 3, 8, 26, 44, .... would look like:

INSERT INTO foobar VALUES (3,42), (8,42), (26,42), (44,42), ...;

My second attempt was to update the foo_count table after every inserted bar object:

INSERT INTO foo_amount (SELECT foo_id, 1 FROM foobar WHERE bar_id = 42)
ON DUPLICATE KEY UPDATE amount = amount + 1;

But this is very slow. Do you have any ideas on how to optimize this? An option might be to accumulate new bar's in a temporary foo_count_tmp and merging it with foo_count every now and then. The foo_count table wouldn't be up-to-date all the time, but that's ok. But how would I trigger the updating then?

2 Answers 2


How about a GROUP BY count on foobar from scratch ???

First, insert any new data into foobar

Then, do a fresh GROUP BY count on foobar into the temp table:

CREATE TABLE foo_amount_new LIKE foo_amount;
INSERT INTO foo_amount_new
SELECT foo_id,COUNT(1)
FROM foobar WHERE bar_id = ... 
GROUP BY foo_id;

Finally, swap the temp table in and drop the old foo_amount

ALTER TABLE foo_amount RENAME foo_amount_zap;
ALTER TABLE foo_amount_new RENAME foo_amount;
DROP TABLE foo_amount_zap;

However, with a table in the billions, this is an uphill battle because you have an index to rebuild. Since the following happens on every INSERT ... ON DUPLICATE KEY:

  • the amount would have to incremented
  • the amount would have to shift with in the amount index

Try removing the amount index so as to speed up INSERTs and UPDATEs.


Try out your temp table solution using another method

STEP 01) CREATE TABLE foobar_new LIKE foobar;

STEP 02) Do your bulk INSERTs into foobar_new

STEP 03) CREATE TABLE foo_amount_new LIKE foo_amount;

STEP 04) Perform GROUP BY count on the latest bulk INSERT batch

INSERT INTO foo_amount_new
SELECT foo_id,COUNT(1) FROM foobar_new WHERE bar_id = ... 
GROUP BY foo_id;

STEP 05) Perform a bulk INSERT into foobar from foobar_new

INSERT INTO foobar SELECT * FROM foobar_new;

STEP 06) Perform a bulk UPDATE of foo_amount from foo_amount_new

UPDATE foo_amount A INNER JOIN foo_amount_new B
USING (foo_id) SET A.amount = A.amount + B.amount;

STEP 07) Drop the temp tables

DROP TABLE foobar_new;
DROP TABLE foo_amount_new;
  • Well.. it's recomputing the whole thing... but at least it won't block anything. I could give it a try. And I need the index on 'amount'...
    – Ben
    Commented Aug 6, 2012 at 16:19
  • I added INSERT IGNORE INTO foo_amount (SELECT foo_id, 0 FROM foo_amount_new) to handle foo_id's not yet present in the table (e.g. when adding the first bar object). It took 20min(!) to add 1000 bar's (170K rows in foo_amount).
    – Ben
    Commented Aug 7, 2012 at 9:54
  • I changed the engine from ndbcluster to myisam and the same bulk update takes less than 20s now :)
    – Ben
    Commented Aug 7, 2012 at 12:28

Is the following query your typical one?

INSERT INTO foobar VALUES (3,42), (8,42), (26,42), (44,42), ...;

If so, and I'm assuming this is generated by code (not by hand), then I would suggest you could build the next query:

UPDATE foo_amount SET amount=amount+1 WHERE foo_in IN (3, 8, 26, 44, ...);

But some things are not quite clear to me:

  • Are the INSERTs guaranteed to work? I mean, can the INSERT INTO foobar VALUES (3,42), (8,42), (26,42), (44,42) contain a duplicate, thus failing the operation?

  • And if you're using some soert of IGNORE, then that complicates your understanding of whether you should increment the amount in foo_amount (applies to your solutions as well)

  • Last, what you're doing is essentially managing summary tables. I don't mean to say you shouldn't -- but are you sure you absolutely need them? can you perhaps just fetch the data when required? It may yet prove to be overall more efficient than managing all the writes. "efficient" is something of a blur here, of course, since you need to decide who gets the higher priority for optimization: reads or writes.

  • Your 'UPDATE' statement is what I am doing with my 'INSERT ... ON DUPLICATE KEY UPDATE' statement. I have to do it this way, because my values to be incremented need a start value. It works, but as I mentioned, it is very slow. An yes: I need this table. I'm querying this table around 200 times per second. Computing the sums on the fly is not an option.
    – Ben
    Commented Aug 7, 2012 at 8:09
  • I see. Just as a quick note, I realize the UPDATE I'm suggesting won't do because of nonexisting rows, but it is very different from your INSERT...ON DUPLICATE KEY, where you issue n such INSERT queries, as opposed to 1 UPDATE query. Commented Aug 7, 2012 at 9:07
  • In my last INSERT statement I'm inserting all foo_id's with bar_id = 42 at once.
    – Ben
    Commented Aug 7, 2012 at 9:19
  • You are right, I missed this. Commented Aug 7, 2012 at 10:01

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.