CODEWITHSUNDEEP
phpmysqlmysqli
Asgarnian
Asgarnian

Reputation: 155

mysql UPDATE multiple rows multiple keys Efficiency

I already got this working, however I'm asking if I could do it better (preferably somehow reduce the amount of all the conditions in WHERE) or if there is a way that takes less execution time (although this seems to work much faster than PHP looping every single update query).

I have to update multiple rows (will probably be between 100-10000 once in production) in a table with 7 primary keys (yeah..i'm almost at a point where I want to just stick a one primary id key on it, but I'd have to go back and recode so much of my browser game then...) I can't use INSERT .. ON KEY DUPLICATE because I shouldn't insert the update if it doesn't exist anymore (doing some REMOVEs on the same table just before updates)

My current solution:

UPDATE planet_market SET

amount = CASE
WHEN resource_id=1 AND type=0 AND price=2000 AND player_id=-1 AND planet_id=1 AND owner_type=2 AND factory_id=70 THEN 1
WHEN resource_id=1 AND type=0 AND price=2700 AND player_id=-1 AND planet_id=1 AND owner_type=2 AND factory_id=161 THEN 181
ELSE amount END

WHERE (resource_id=1 AND type=0 AND price=2000 AND player_id=-1 AND planet_id=1 AND owner_type=2 AND factory_id=70) OR (resource_id=1 AND type=0 AND price=2700 AND player_id=-1 AND planet_id=1 AND owner_type=2 AND factory_id=161)

So is there a better (shorter query) or faster (execution) way to do this?

Upvotes: 0

Views: 159

Answers (1)

Asgarnian
Asgarnian

Reputation: 155

Well I managed to find a way to considerably shorten the query while it works pretty much the same (I hope..., my limited testing seem to perform the same, please correct me on that), so i'm posting my own answer...

Here's how (don't mind the slightly different data used, notice the structure change):

UPDATE planet_market SET

amount = CASE
WHEN resource_id=3 AND type=0 AND price=2147483647 AND player_id=9 AND planet_id=2 AND owner_type=0 AND factory_id=-1 THEN amount + 1
WHEN resource_id=4 AND type=0 AND price=250000 AND player_id=8 AND planet_id=2 AND owner_type=0 AND factory_id=-1 THEN amount + 1
ELSE amount END

WHERE (resource_id, type, price, player_id, planet_id, owner_type, factory_id) IN ((3,0,2147483647,9,2,0,-1),(4,0,250000,8,2,0,-1))

Any other ideas?

I hope this helps anyone else looking for a solution like this (I wasn't able to find any any other info on stackoverflow about updating multiple rows when you have multiple keys)

I wonder if I can shorten the query by using WHERE (column1,column2,...) IN (data1,data2,...) inside the WHEN THEN ... gonna check that out next (I would only shorten it with the lack of "=" ... lol)

Upvotes: 1

Related Questions