Skip navigation links

MySQL Forums :: Performance :: Optimize multiple writes on the same row


Advanced Search

Re: Optimize multiple writes on the same row
Posted by: Rick James ()
Date: March 15, 2012 10:41AM

One thought...
Write an "UPDATE" TRIGGER on the 'fact' (million-row) table. It would add NEW.val - OLD.val to a sum column in another table (perhaps only 1 row). (Do all of the updates in a single UPDATE in the TRIGGER.)
Then your 'report' SQL would hit only this new table.

Write an "INSERT" TRIGGER also. It would do similar things, but also modify the 'count' field in the secondary table.

Note: Do not keep an 'average' field; instead, always recompute it (sum/count). Standard deviation can be done if you keep three totals: count, sum, and sum of squares.

Median is actually possible, but tricky.

I am assuming that the complexity of this scheme would be less costly (on performance) than repeatedly scanning a million rows.

How often do UPDATEs come in? How often for INSERTs? And DELETEs?

Options: ReplyQuote


Subject Views Written By Posted
Optimize multiple writes on the same row 1237 Francis Page 03/14/2012 02:16PM
Re: Optimize multiple writes on the same row 470 Rick James 03/15/2012 10:41AM
Re: Optimize multiple writes on the same row 576 Francis Page 03/16/2012 05:31AM
Re: Optimize multiple writes on the same row 426 Rick James 03/18/2012 10:06AM
Re: Optimize multiple writes on the same row 548 Francis Page 03/19/2012 08:33AM
Re: Optimize multiple writes on the same row 535 Rick James 03/20/2012 11:32AM
Re: Optimize multiple writes on the same row 415 Francis Page 03/22/2012 11:49AM
Re: Optimize multiple writes on the same row 626 Rick James 03/23/2012 11:35AM


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.