1

I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up?

I am using following approach:

mysql -uroot < query_file

where query_file contains 1M update queries.

The queries are like:

update table a set col1=1 where col2="something";

Please note, that the value of col2 is different for each query.

What would be the fastest approach to bulk update this mysql table (Innodb)?

Moreover, can I optimize this query like by col2 as a primary key or unique or index or anything else, you can suggest?

UPDATE:

Table has 8 columns.

Right now, the Primary key is a combination of url_hash(16) and domain name(say col 1 and col 6). Though, I am planning to add auto-increment id column as the primary key (col2 in the above example)

Table has 4 BTREE Type index:

  • Index 1 on say col 1 and col 6 <-- primary key
  • Index 2 on say col 1, col 3 and col 4
  • Index 3 on say col 1, col 5
  • Index 4 on say col 4
2
  • 1
    4 questions: Do you have an index on col2? Do you have a lot of other indexes? Will those updates change all queries or just a subset? Is the new value of col1 always different from the old value?
    – Jannes
    Feb 10, 2012 at 22:08
  • Indexes detail added above. Each update are going to do just one unique row in the table. col1 value can only be 1 or 0. It might be possible that col1's old and new value are same. Feb 10, 2012 at 22:58

2 Answers 2

2

The first thing you need to determine about col2 is if it can be a PRIMARY KEY.

Run this query

SELECT COUNT(1),col2 FROM table GROUP BY col2 HAVING COUNT(1) > 1;

If nothing comes back, then col2 can be a UNIQUE KEY. If even one row comes back, then col2 cannot be a UNIQUE KEY. You can create an index on it.

Since this query would take a while without an index, just go ahead and index it

ALTER TABLE table ADD INDEX col2 (col2);

but that might take a very long time.

You may need a temp table to accomplish this:

Step 01) Create a table that will hold the one million col2 values

DROP TABLE IF EXISTS col2values;
CREATE TABLE col2values ENGINE=InnoDB SELECT col2 FROM table WHERE 1=2;
ALTER TABLE col2value ADD PRIMARY KEY (col2);

Step 02) You can load the col2values table with the one million col2 values

Step 03) Index col2

CREATE TABLE table2 LIKE table;
ALTER TABLE table2 ADD INDEX col2 (col2);
INSERT INTO table2 SELECT * FROM table;
DROP TABLE table;
ALTER TABLE table2 RENAME table;

Step 04) Perform update using INNER JOIN

UPDATE col2values A
INNER JOIN table B
USING (col2)
SET B.col1=1;

In the above steps

  • Step 03 needs to be done only once
  • Step 03 is done with 5 lines rather than ALTER TABLE table ADD INDEX col2 (col2); because it would require a full rollback operation should anything go wrong.
  • All other steps are your bulk insert steps going forward

Give it a Try !!!

1

Adding an index for col2 will definitely help you a lot. PRIMARY KEY or UNIQUE would be best in your case.

It's usually (always?) better to have a single (auto-increment) numeric column as the primary key. You can make col1+col6 a UNIQUE key instead.

Your Answer

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

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