-1

I'm having a huge issue with a process I'm doing and I'd like to know what can I do to solve it. The table's engine is InnoDB.

I have a table that has about 10 million records and I need to update most of the rows after doing some processing with some values that I browse for first. The information processing itself (without updates) is fairly fast, taking just a few minutes, however if I add an update query that modifies each row, the times goes up for hours.

To give a clearer idea, the table contains every single movement of every product the database has. The process calculates the average cost of a product depending of the purchases of said product. That process is done quickly however I need to update the column in that same table that contains the average cost in each row so it is an update per process and it can be up to several millions making the times skyrocket. How can I possibly solve that? Thank you.

I have absolutely no idea how to paste table info in a really neat formatted way so here are some screenshots: screenshot1 screenshot2

About the UPDATE statement is as simple as it gets

UPDATE kardex SET cos_promedio = X WHERE kardexID = Z
10
  • Can you post the table definition?
    – Dave
    Jan 7, 2016 at 0:00
  • @dave done! i'm not sure if that's what you were asking for haha.
    – Hikaros
    Jan 7, 2016 at 0:18
  • Generally speaking, you improve performance of an UPDATE statement by adding some useful indexes and/or rewriting the statement. Since you did not show the statement or table/index definitions, I doubt a more concrete answer will be forthcoming.
    – mustaccio
    Jan 7, 2016 at 1:04
  • Excuse the ignorance lol but isn't the table definition what is shown in the screenshots i posted? About the UPDATE statement is as simple as it gets UPDATE kardex SET cos_promedio = X WHERE kardexID = Z
    – Hikaros
    Jan 7, 2016 at 1:10
  • 1
    @Hikaros are all the averages supposed to be different for different rows (even for the same product)? Some kind of history of averages.. (then RJ's answer is the way to go) Or do you just compute one average per product and then fill the same value to all rows/purchases of that product? (in such case just store the average in some products table without repeating)
    – jkavalik
    Jan 7, 2016 at 9:24

1 Answer 1

2

Think out of the box.

Don't keep that column in the table. Instead, put it by itself (plus an id for joining) in another table. Then your processing is to recreate that other table without modifying the main table.

You say "most" of the rows are updated? Do you mean that you recalculate all the rows, but not all of them change? In that case, let's call the extra table xyz...

CREATE TABLE new_xyz ( id..., cos_promedio ... );
INSERT INTO new_xyz
    SELECT id, AVG(...) FROM ...;
RENAME TABLE xyz TO old_xyz, new_xyz TO xyz;
DROP TABLE old_xyz;

This will by much faster than the UPDATE.

0

Your Answer

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

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