0

I have a simple tag_map

CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB

The table is quite big (over 900 million rows). Obviously, INSERTing into the empty table is quite fast, but when I add more rows, it takes a long time (since it needs to check for duplicates).

INSERT IGNORE INTO tag_map (article_id,tag_id)
    SELECT article_id,tag_id FROM t1 WHERE article_id>0 AND tag_id>0

I did not run a side-by-side benchmark, but ARIA table is about 2-3 times faster for adding more rows.

I wonder if there are tricks to make InnoDB faster for this?

The relevant configs are

innodb_buffer_pool_size = 50G
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0

1 Answer 1

1

Two things that each might speed it up:

  • Add secondary keys after building the table. That is, leave off INDEX(tag_id) and do an ALTER to add the index later.

  • Sort and de-dup the incoming data. That is

      INSERT INTO ...
          SELECT DISTINCT ...
              ORDER BY article_id, tag_id;
    

The IGNORE requires a lookup to verify that the row is not already there. DISTINCT has the same effect, but is done in bulk, not one row at a time.

What is the relative size of the Aria table vs InnoDB?

In my experience, "tags" should not be normalized. That is, I would simply have (article_id, tag), not tag_id linking to another table. That extra JOIN costs something.

Another tip: If you will be sorting articles for display, especially with LIMIT and OFFSET, there are certain other inefficiencies. I discuss this in Lists . (That talks about "topics" in place of "tags".)

5
  • I don't follow. You are talking about the INSERT INTO empty table? My problem is when adding more rows. I need INDEX(tag_id) and IGNORE to skip duplicate rows (the rows that already exist in tag_map not duplicates in t1 to be filtered by DISTINCT). The InnoDB table is about 1.5 times larger than the ARIA table.
    – Googlebot
    Aug 1 at 23:30
  • @Googlebot - Sorry, both of my suggestions assume an empty table.
    – Rick James
    Aug 2 at 2:25
  • FYI, for a 1.5 billion-row table, InnoDB's ibd file is 141.7GB, Aria's MAD 13.6GB, and MAI 36.4GB. Thus, the exact answer is InnoDB table is almost 3 times larger.
    – Googlebot
    Aug 2 at 3:48
  • @Googlebot - Thanks. 2x to 3x is what I have measured many times for InnoDB :: MyISAM. So, I guess Aria is somewhat similar to MyISAM.
    – Rick James
    Aug 2 at 3:54
  • I have experimented a lot. MyISAM is slightly smaller than Aria. This is the biggest difference between the size of InnoDB and MyISAM/ARIA since there are two small indexed columns. I store mediumtext tables by Aria, and the difference is less than 1.5 times. There is not much storage benefit, but reading from Aria on HDD is 10 times faster than InnoDB.
    – Googlebot
    Aug 2 at 3:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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