AleksCore

MySQL Tips (need help)

Recommended Posts

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 by AleksCore
updated

Share this post


Link to post

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

  • Like 1

Share this post


Link to post

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 by NegativeIQ
changed table name from Players to race_stats
  • Like 1

Share this post


Link to post
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 by AleksCore
For some reason thought it won't work but it works

Share this post


Link to post

@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 by NegativeIQ
  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.