0

Lets say I have a table user|product|amount

My goal: to update the amounts of the products the user has in his shopping cart, removing the records which have amount 0.

What is more efficient:
A) Deleting records from database; inserting new ones [to sum up: 1x delete query, 1x big insert query]
B) Updating all values to zero; updating only the needed ones to their values; deleting all records which have a value of zero [[B]1x update query, ~10x update queries[/B], (because I don't know how to update many at once(for example update cart set amount=5 where product=3 and user=1), but I doubt it is possible to update more than one in the sentence like this), [B]1x update query[/B]]

So I think A) is better winning 2 to 12. Am I right?

P. S. Should be around 10 updated and/or removed records every time. Values are all integer (11).

(Also, such cases can be executed very often, so every millisecond is of great importance)

Thanks for helping me learn!

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by smantscheff
0

First I recommend that you test your variants for speed and efficiency. Then have a look at the EXPLAIN explanations for your update queries which might tell you about some potential inefficencies. Third, I assume you have an unique user/products index. Therefore you could use an INSERT IGNORE query to add a user/product pair and then update this row. With a unique primary index overhead should be minimal. Then you could delete empty rows in an cronjob on an hourly or daily base.
You can of course update more than one row at once, but with unique user/product tupels you will have one update query for each product per user.

Edited by smantscheff: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.