Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm writing an application that needs to flush out a large number of updates to the database for an extended period of time, and I've gotten stuck at how to optimize the query. Currently I'm using INSERT INTO ... VALUES (..), (..) ON DUPLICATE KEY UPDATE, which works to batch all of the values into one query, but executes excruciatingly slowly on large tables. I don't ever actually need to insert rows.

Other approaches I've seen are to update using SET value = CASE WHEN... (which would be hard to generate due to the way I'm building the queries, and I'm not sure about the performance of CASE for hundreds/thousands of keys), and simply multiple concatenated updates. Would either of these be faster than my current method?

It baffles me that, as far as I can tell, there's no idiomatic, efficient way to do this in MySQL. If there really isn't a way that's faster than ON DUPLICATE KEY, would it be worth it to switch to PostgreSQL and use its UPDATE FROM syntax?

Any other suggestions are also greatly appreciated!

Edit: here's one of the tables that gets updated frequently. I've removed column names due to them being irrelevant.

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` bigint(20) unsigned NOT NULL DEFAULT '0',
  `b` bigint(20) unsigned NOT NULL DEFAULT '0',
  `c` enum('0','1','2') NOT NULL DEFAULT '0',
  `d` char(32) NOT NULL,
  -- trimmed --
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` (`c`),
  KEY `d` (`d`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
share|improve this question
    
Please provide SHOW CREATE TABLE -- the issue may be with the indexes. Also, what is the value of innodb_buffer_pool_size? It should be about 70% of available ram (assuming you are using InnoDB). Is "UPDATE FROM" anything like putting the values into a MEMORY table, then using a "multi-table" UPDATE to transfer them into the 'real' table? Plus an INSERT SELECT to grab the 'new' rows? –  Rick James Nov 7 '12 at 1:08
    
This is on a testing machine and not on production so InnoDB isn't completely tuned properly. I'm not totally sure about how INSERT FROM operates, but what you said seems about right. Updated the question with the info you asked for. –  jli Nov 7 '12 at 1:27
    
@RickJames Please only post answers to the question as an answer - if you need more information from the OP then the comments are the right spot to ask. –  JNK Nov 7 '12 at 14:05
    
You "don't ever actually need to insert rows"? Then why are you doing an INSERT? How does it all work if you do an UPDATE? –  Alain Collins Nov 7 '12 at 17:05
    
@AlainCollins I'm doing the insert to batch a ton of updates into one database query. I'm asking if there's a better way to do that. –  jli Nov 7 '12 at 17:47
show 3 more comments

1 Answer

Since you're using InnoDB tables, the most obvious optimization would be to group multiple UPDATEs into a transaction.

With InnoDB, being a transactional engine, you pay not just for the UPDATE itself, but also for all the transactional overhead: managing the transaction buffer, transaction log, flushing the log to disk.

If you are logically comfortable with the idea, try and group 100-1000 UPDATEs at a time, each time wrapped like this:

START TRANSACTION;
UPDATE ...
UPDATE ...
UPDATE ...
UPDATE ...
COMMIT;

Possible downsides:

  • One error will collapse the entire transaction (but would be easily fixed in code)
  • You might wait for a long time to accumulate your 1000 UPDATEs, so you might also want to have some timeout
  • More complexity on your application code.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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