MySQL Forums
Forum List  »  InnoDB

Speed of Update Commands
Posted by: Lucio Menci
Date: January 27, 2015 09:24AM

Hi,

I want to save an invoice, with about 50 rows, with about 50 fields each.
I noticed that, if I want to update already saved rows, mysql needs about 8รท12 times than to delete all rows and insert them.

I.E.:
Delete from InvoiceRow where invoicekey = ?invoicekey
insert into InvoiceRow (InvoiceKey, RowNr, Descr, Qty, Price, [...])
Values (?InvoiceKey, ?RowNr, ?Descr, ?Qty, ?Price, [...])

Need less than 1 second.

Update InvoiceRow Set Descr = ?Descr, Qty = ?Qty, Price = ?Price, [...] Where InvoiceKey = ?InvoiceKey And RowNr = ?RowNR
Insert the unpresent ones, Delete the odds
Need about 8 seconds

I know it should be for updating indexes. I like much more the second method, because I can leave unknown fields of already existing rows untouched.
I tried to set Lock Tables to lock and release indexes, but the taken time does not change. Furthermore, if there is an error, the rollback doesn't work.

Is there a way to tell InnoDB to allow the Rollback and make it to freeze indices and release them after the last update?

Thank you

Options: ReplyQuote


Subject
Views
Written By
Posted
Speed of Update Commands
1680
January 27, 2015 09:24AM
976
January 28, 2015 10:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.