Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » InnoDB DELETE/INSERT vs. UPDATE in transaction
InnoDB DELETE/INSERT vs. UPDATE in transaction [message #1317] Fri, 25 May 2007 09:51 Go to next message
puzzles is currently offline puzzles
Messages: 2
Registered: May 2007
Junior Member
Hi,

I have an InnoDB table with approximately 300 rows, which is regenerated every 3 seconds. (This is a redundant statistics table for a website with a *very* heavy load.)

Right now, the generation of the table is done in a transaction by deleting all rows and then inserting all necessary new ones. My question is, would it be faster (does it make any difference at all) to not DELETE/INSERT the rows, but do an INSERT ... ON DUPLICATE KEY UPDATE query and then delete the rows that were unaffected by this operation (and thus, are now obsolete)?

Thanks.
Report message to a moderator
Re: InnoDB DELETE/INSERT vs. UPDATE in transaction [message #1320 is a reply to message #1317 ] Sat, 26 May 2007 06:15 Go to previous messageGo to next message
Speeple is currently offline Speeple
Messages: 91
Registered: August 2006
Member
Depends on the cardinality.

If by using INSERT .. ON DUPE.. leaves you DELETING 80% of rows, performance might suffer.

Maybe in that case it's best to do TRUNCATE->CREATE->INSERT.

300 rows isn't too large a data set to populate (depending on datatypes used).

Only way to get a definitive answer is to benchmark each method.

Martin Gallagher | Speeple: The latest news
Report message to a moderator
Re: InnoDB DELETE/INSERT vs. UPDATE in transaction [message #1324 is a reply to message #1317 ] Sat, 26 May 2007 08:19 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
And with the TRUNCATE->INSERT alternative you avoid searches that has to happen for each UPDATE/DELETE query.

So for a table with 300 rows where you also will delete the rows that was affected I think you are best off with trunacting and inserting the new recods.
Report message to a moderator
Re: InnoDB DELETE/INSERT vs. UPDATE in transaction [message #1329 is a reply to message #1317 ] Sun, 27 May 2007 17:59 Go to previous messageGo to next message
inner is currently offline inner
Messages: 13
Registered: November 2006
Location: Lithuania
Junior Member
Puzzles,

I know this is not an answer to your question, but why not use HEAP instead of InnoDB? This would completely eliminate disk I/O and as the content is regenerated every few minutes, you should not really care too much about loosing data. As update is relatively fast, table locks should not be an issue here.

As for DEL/INS vs UPDATE, you could take few thousand samples from binary logs (to match ± average workload) and run few benchmarks (probably by running two copies of SuperSmack - one for sequential DEL/INS or UPDATE procedures and the other one - for concurrent SELECT workload).
Report message to a moderator
Re: InnoDB DELETE/INSERT vs. UPDATE in transaction [message #1333 is a reply to message #1317 ] Tue, 29 May 2007 04:42 Go to previous message
puzzles is currently offline puzzles
Messages: 2
Registered: May 2007
Junior Member
Hi all,

Thanks for your replies.

The reason I chose InnoDB and the DELETE/INSERT method is that it's very important that we get consistent data from the database at all times. With InnoDB, I can do my updates (DEL/INS) in a transaction and then COMMIT the changes in one step, meaning that all reads from the table will result in "correct" data.

The problem with the MEMORY (or HEAP) storage engine is that it doesn't support transactions, so in that case, the DEL/INS method is no longer usable. (Having people read an empty table, even for just a split second is not an option here.) The same goes for TRUNCATE, as you can't use it in a transaction, since it drops and recreates the table.

Anyway, thanks for your suggestions. I'll try benchmarking both methods and see what I come up with that way.

Thanks.
Report message to a moderator
Previous Topic:DISTINCT vs GROUP BY
Next Topic:MyISAM to INNODB..
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Sun Jul 5 22:31:51 EDT 2009

Total time taken to generate the page: 0.04174 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.