Running Multiple MySQL Statements slowing down server | SpigotMC | Page 1

short cloud Mar 30, 2022, 8:40 AM

#

Hey guys!

I'm extremely new to MySQL. I started using it a few days ago to store data for players on the server. It's a simple table called user_info with 2 columns, uuid and nickname. Every time I want to get the nickname of a player, I query the database with something like this:
SELECT nickname FROM user_info WHERE uuid=<some_uuid>

Then from the ResultSet I use getString. This works fine, however there is a very slight delay between executing the query and receiving the string. For single users this is hardly noticeable, but if I want to get the nicknames of 100 players at the same time, I need to send 100 queries to the Database and then you start to notice a very significant delay of a few seconds, in which nothing on the server can happen. If too much of this happens the console gets mad for "running behind".

So... am I doing something wrong? Is a delay like that normal for selecting rows from MySQL? If so, what's a better way to grab information from 100 separate select statements with minimal delays?

Please let me know if you need anymore information about my code.

Thanks 😄

fallow goblet Mar 30, 2022, 8:57 AM

#

instead, how about when the server starts up, you query all the nicknames and uid's into a map of some sort, then it can be accessed a lot quicker in memory, then when the server disables check for any changes and update the database

#

then you'll only have to query the database when the server starts up and shuts down, less lag while the server is running?

short cloud Mar 30, 2022, 8:58 AM

#

I considered that, but I have 4k+ rows in my table, is it really a good idea to store that much data (and possibly more in the future) in memory?

fallow goblet Mar 30, 2022, 8:58 AM

#

ah, that is quite a lot of people

short cloud Mar 30, 2022, 8:58 AM

#

¯_(ツ)_/¯

fallow goblet Mar 30, 2022, 8:59 AM

#

I am not a SQL guru either, hence why that's all I suggested as thats what I do with my flatfiles so that I am not reading and writing files all the time

fallow goblet Mar 30, 2022, 9:00 AM

#

short cloud I considered that, but I have 4k+ rows in my table, is it really a good idea to ...

could you test it easily? I mean its not a lot of data to have a uid and a nickname

short cloud Mar 30, 2022, 9:00 AM

#

wdym 'test' it?

#

oh sorry I misread the message lol

opaque gate Mar 30, 2022, 9:02 AM

#

Make sure to run it asynchronously so the server won't hang. Other than that there's not much you can do here since it's a proper way to retrieve the data

short cloud Mar 30, 2022, 9:02 AM

#

ye I could try, although I don't really know what to look for. And even if it did work with 4k entries... I want to be able hold much much more in case the server grows

#

^ replying to tanko_

opaque gate Mar 30, 2022, 9:04 AM

#

You can still cache the player data, but if you have a lot of players, only cache the player on join and remove them when they quit

short cloud Mar 30, 2022, 9:04 AM

#

I'm already doing that, but in this case I'm specifically querying offline players...

opaque gate Mar 30, 2022, 9:04 AM

#

What's it for?

short cloud Mar 30, 2022, 9:05 AM

#

each player has a friends list. I'm just trying to make it show a gui, which contains all the heads of the player's friends... and each head should be named the users nickname. Only way to do that is to select the nickname for each friend from the database, since more often than not, the friends will be offline

#

and the player could end up having a ton of friends. Currently I added 100 friends, and it takes a good few seconds for the gui to open... which is pretty annoying

opaque gate Mar 30, 2022, 9:16 AM

#

what is the query you use to get all friends of a user?

short cloud Mar 30, 2022, 9:19 AM

#

that's stored in a seperate table, and I use:
SELECT friend FROM user_friends WHERE uuid=<some_uuid>
And then I use getStringList to get a list of the uuids.

I confirmed this isn't the part causing the lag, because 1) it's only 1 select statement 2) If I remove the code which renames the heads to the corresponding nickname, there is no delay

opaque gate Mar 30, 2022, 9:21 AM

#

oh friend is a list?

#

that does make it a little harder

#

because this way you have to first execute this to get the friends, then send another query for each individual uuid in that list to get the nickname

short cloud Mar 30, 2022, 9:22 AM

#

when I add a friend, it adds a new row into the table where uuid is my uuid, and friend is the uuid of my friend. For each new friend a new row is created. The select statement will return all rows where the uuid is mine, so the getStringList is returning all the values in the friend column

short cloud Mar 30, 2022, 9:23 AM

#

opaque gate because this way you have to first execute this to get the friends, then send an...

yeah exactly... that's the problem :P

opaque gate Mar 30, 2022, 9:23 AM

#

it's more sufficient if you were to store each friend on a new row so you can use an inner join so it's only one query

#

then you just iterate through the resultlist and voila

short cloud Mar 30, 2022, 9:24 AM

#

What is an "inner join" 😅

opaque gate Mar 30, 2022, 9:25 AM

#

an inner join is a way to get data from separate tables using joined data (column), which in your case would be the uuid

short cloud Mar 30, 2022, 9:25 AM

#

oh! Well that could solve a lot of my problems

#

I'll look into that :)

opaque gate Mar 30, 2022, 9:26 AM

#

so let's say this is your database structure:

user	friends
uuid	uuid
nickname	friend

#

then you can do

SELECT nickname FROM friends INNER JOIN user ON friends.friend = user.uuid WHERE friends.uuid = ?```
Here the `?` would be the user you're looking up the friends of.

#

Then you just iterate through the resultlist to get all the nicknames of their friends

short cloud Mar 30, 2022, 9:28 AM

#

ahhhh that makes a lot of sense

#

so that would only return a list of nicknames, if I wanted to also just return the uuids of the friends, that would be need to be a separate select?

#

like I was doing previously

opaque gate Mar 30, 2022, 9:29 AM

#

No, you can simply add all the columns to the select

short cloud Mar 30, 2022, 9:29 AM

#

oh right

opaque gate Mar 30, 2022, 9:29 AM

#

so you can do nickname, friend

#

if there's multiple columns with the same name across the tables, use the table name as prefix by following: table.column

short cloud Mar 30, 2022, 9:30 AM

#

man this is insanely helpful, tysm 🙏

opaque gate Mar 30, 2022, 9:30 AM

#

Sure thing!

short cloud Mar 30, 2022, 9:43 AM

#

Ah I just realized in a different gui, I'm displaying all the groups each friend has, but I don't think an inner join will work there...

All the user's groups are stored in another table called groups with the structure, uuid and group. To get the group names for that user I use
SELECT group FROM groups WHERE uuid=?

Is there any better way in this case to get all the groups of all the friends, other than first getting the list of friends, and then individually getting the groups for each friend?

opaque gate Mar 30, 2022, 11:59 AM

#

What is a group? What information does it hold?

wraith minnow Mar 30, 2022, 1:34 PM

#

you could try using a h2 embedded database instead of mysql

#Running Multiple MySQL Statements slowing down server