MySQL Tips (need help)

[AleksCore]

I've been searching for a MySQL tips and noticed there is no such topics on this forum. So let's fix this little problem.

I want to know more about optimising MySQL queries and how to interract with MySQL correctly, I am newbie in that.

I learned that putting queries in the loop it's too performance-expensive, wrong, and very big chance it'll freeze your server for uncertain time. 

So if I want to get information from DB for a many players, better to make it using only 1 query. Example:

"SELECT * FROM race_stats WHERE playerID IN(??)"

Where "??" = table with logged in players.

But, what if I want to update information for a many players at the same time? How such query should look like?

For 1 player it looks like this:

"UPDATE race_stats SET id1 = id1 + ?, id2 = id2 + ?, id3 = id3 + ?, id4 = id4 + ?, id5 = id5 + ? WHERE playerID = ?"

But how to make the same for all players? Put query in the loop will be too performance-expensive I guess and will create laggs.

 

P.S. Maybe for such thing as player stats better to learn MySQL procuderes and create procedure for this, I am newbie, just asking. Don't even know what is procedures exactly, just heard about it.

Edited September 30, 2016 by AleksCore
updated

[pa3ck]

Procedures or stored procedures (sprocs) are basically SQL functions (methods) and they work the same way as a LUA function does. They have inputs, outputs, if statements and loops and a whole lot of other things.

 

For multiple WHERE values, you can use the SQL IN operator. 

For your other question, have a look at this topic: update-multiple-columns-for-multiple-rows-in-one-query-of-sql

[AleksCore]

Thanks for the reply, but I'll try to use multi statements and see how it works. Need to enable it in dbConnect and use dbPrepareString

[NegativeIQ]

You can use same IN statement for update same way as you use it for select.

UPDATE `race_stats` SET `Info`=2, `Info5`='SomeString' WHERE `playerID` IN(??)

Since these topic is about tips, my tip is to always escape table and field names in mysql to avoid errors (see how i use tilde and single quotes in that code). Also escape any kind of user input.

Edited September 30, 2016 by NegativeIQ
changed table name from Players to race_stats

[AleksCore]

18 minutes ago, NegativeIQ said:

You can use same IN statement for update same way as you use it for select.

UPDATE `race_stats` SET `Info`=2, `Info5`='SomeString' WHERE `playerID` IN(??)

Since these topic is about tips, my tip is to always escape table and field names in mysql to avoid errors (see how i use tilde and single quotes in that code). Also escape any kind of user input.

Oh, it actually works. Thank you! I like this way.

@NegativeIQ oh, wait. But what to do if data different for all players? Same columns but different data

Edited September 30, 2016 by AleksCore
For some reason thought it won't work but it works

[NegativeIQ]

@AleksCorethen you have no choice but to send multiple queries. And by the way just so that someone doesnt say im wrong, there are some languages or plugins/extensions for "things/languages" that allow sql multi query but its highly discouraged since its more trouble than its worth and it actually quite hard to sanitize from sql injection. So in the end its like you dont have a choice but to simply send multiple queries (and its also easier to handle errors these way). Also considering that we are talking about mysql and how it works, when you send queries one after another and only change values it should reuse previous query cache so second one would be allot faster (but that could also depend on mysql configuration).

Feel free to loop thru all players while using dbQuery, its a non blocking function (wont pause code execution while waiting for mysql response).
And since you are just updating and dont care about result (there is none) just use dbFree right after dbQuery function. (check dbQuery wiki for more info)

Edited October 1, 2016 by NegativeIQ

[AleksCore]

Thank you, @NegativeIQ.  I guess I can use dbExec and don't need to free db in this case