Few little questions about MySQL

[John Smith]

Hi. I haven't really used MySQL in MTA so I don't really know how well it performs so I'm going to ask you guys a couple of questions (please answer only if you have actual experience with this or are sure that it is correct)

If my MySQL database had a table consisiting of 20000+ stuff inside it, lets say accounts

And i need to check specific account's data from MySQL or change it, when would it be best to do so? There are more than 20 thousand entries for accounts inside of that MySQL table, so wouldn't looking for specific account be kinda performance impacting to server? So when would be the appropriate time to access MySQL data in that case? Or is MySQL really fast so it doesn't matter that much?

I don't really know much about MySQL since I haven't really used it so I'm asking you guys to help me out to learn some stuff about it. Oh and if there are some topics here explaining this sort of stuff regarding MySQL please show me the topic(s) haha

Thanks for reading

[Addlibs]

I'm pretty sure if you use LIMIT 1, it won't have a very significant impact on the server (since it will stop looking through the database as soon as it meets first match).

[John Smith]

Well what if by unlucky conditions it happens that specific entry im looking for just appears to be at the bottom of loop? That way it would still have looped through whole table (though this wouldn't happen often)

However I am interested how much does this big kind of looping affect the server and in what way, and which events would be suitable for using MySQL stuff

What if that database wouldn't just have accounts on it, what if it had something more common, like maps for instance, they would change every few minutes, so if server would be looking for map data from MySQL database and if there were couple of thousands maps in MySQL database, it could affect the server..

 

TL;DR: what are the impacts of looping through MySQL table consisting of few thousands or tens of thousands of entries?

[pa3ck]

As long as your database is normalized you shouldn't have a problem with tens of thousands, in fact, millions of records in each table are still fine as long as your queries and data structure is well put together. Take a look at this select speed and see how you can speed up queries. 

On the other hand, if you don't need to use MySQL for something or you know you will use the exact same data in the near future, you should take advantage of LUA tables and cache them client/server side. When I wrote my RolePlay gamemode, I only loaded most of the data once, then cached them for later use (eg. interior enter / exit location, character name etc..)

[John Smith]

Oh, great then, thanks

Are there perhaps some known issues with this databases that I should look out for? If i'd shut down my server, would MySQL be fast enough to save all the data that i need it to save? Trigger for that saving would probably be onResourceStop

[pa3ck]

Well you should probably write your own "shut down handler" which could be a command that saves all data first, then execute the shutdown.

[John Smith]

Alright thanks for all the help

[Addlibs]

58 minutes ago, John Smith said:

Oh, great then, thanks

Are there perhaps some known issues with this databases that I should look out for? If i'd shut down my server, would MySQL be fast enough to save all the data that i need it to save? Trigger for that saving would probably be onResourceStop

If you send the data before the server turns off (onResourceStop - unless it crashes, in which case I don't know if it will work) - assuming the MySQL server doesn't get turned off or crashes before, I think it should all safely save.

[3aGl3]

First of all MySQL is build to handle such data quantities. Unless you get into the range of big data there shouldn't be a problem...now before you ask, big data is usually something like googles meta data on users or similar. (Billions, if not trillions of records) So given that your server will likely never exceed say 50,000 accounts, you may have a table with items or something else that has 5,000,000 records etc. however none of that is even remotely big enough to bring MySQL down...given that you use good keys and queries.

However, you could of course still mess up. Trying to SELECT query the entire items table (5,000,000) items for whatever reason would be a bad idea for example but that's what the LIMIT argument is for. If you build a database for a gamemode you should definitely use a tool like MySQL Workbench. Like that you have a great overview on your tables and can even track the performance of your database server. (There is a free Community edition btw.)

Also as pa3ck wrote, it's probably a good idea to cache certain things in lua tables, for example the locations of houses or similar won't usually change, so loading them when the resource starts is probably a good idea. If they really change, probably an admin placing new houses or so, save as soon as the change is done, that will leave you with little to save when the server shuts down.

Now I don't (yet) have experience with a MTA server actually running on MySQL and having lots of data, however I worked with other games that had a MySQL database and it worked great, even with almost 1,000,000,000 entries in the items table.

 

Edited February 9, 2017 by 3aGl3