18.01.2015, 10:06
Yes, and no. Better server with faster connection between samp and mysql server will increase your queries speed. But, there are many more variables in play here, almost all concerning your database design:
1. Are you indexing critical data correctly?
If you are looking up a lot of strings, this stuff will boost your queries a lot.
2. Is your table layout sane?
MySQL is a RDBMS - relational database management system. Long story short it means: don't store everything in one table, because its specialised in fetching stuff from related tables very quickly. Keeping that in mind, you have to evaluate how your relationships are defined - if you read up about #1, then you have some insight into how indexes work. Do you store player name in every related table? That's awful. String lookups (even with hash maps) are way slower than integer ones. How to deal with it? Once you load user from database by his player name, store the user database id somewhere. Then, when needed use that to fetch data from other tables.
3. Are you using MySQL best assets?
Just to be sure I want to check if you are using relations at all. So, do your queries look like:
Or
4. Are you loading excessive amount of data?
I've seen many times when people were loading stuff which was used literally one time only in certain conditions when player is in the middle of desert and types special command. Multiply that stuff times thousand, and here you go, excessive processing for OPC, while in fact you should lazy-load: that is load that kind of records only when they are first accessed (and then kept in memory as you've executed query already). Very important note though: the actual plugin call to execute query is the heaviest part of this, as the actual query is only fraction of a second, so you have to balance between large amount of data vs small but frequent loadings.
Goddamn, that's a wall of text. I'm no expert, just a few thoughts (and I changed that a few times for no specific reason so there might be some weird jumping between thoughts)
1. Are you indexing critical data correctly?
If you are looking up a lot of strings, this stuff will boost your queries a lot.
2. Is your table layout sane?
MySQL is a RDBMS - relational database management system. Long story short it means: don't store everything in one table, because its specialised in fetching stuff from related tables very quickly. Keeping that in mind, you have to evaluate how your relationships are defined - if you read up about #1, then you have some insight into how indexes work. Do you store player name in every related table? That's awful. String lookups (even with hash maps) are way slower than integer ones. How to deal with it? Once you load user from database by his player name, store the user database id somewhere. Then, when needed use that to fetch data from other tables.
3. Are you using MySQL best assets?
Just to be sure I want to check if you are using relations at all. So, do your queries look like:
pawn Code:
mysql_format(dbhandle, query, sizeof query, "SELECT id, name, foobar FROM users WHERE name = '%e'", playername);
mysql_tquery(dbhandle, query, "SomeCallback", "i", playerid);
//... Process it somewhere else
mysql_format(dbhandle, query, sizeof query, "SELECT id, name, fizzbuzz FROM user_fizzbuzzes WHERE name = '%e'", playername);
mysql_tquery(dbhandle, query, "SomeCallback", "i", playerid);
pawn Code:
mysql_format(dbhandle, query, sizeof query, "SELECT u.id, u.name, u.foobar, ufb.fizzbuzz FROM users u LEFT JOIN user_fizzbuzzes ufb ON ufb.user_id = u.id WHERE name = '%e'", playername);
mysql_tquery(dbhandle, query, "SomeCallback", "i", playerid);
//Fetch result from both tables
I've seen many times when people were loading stuff which was used literally one time only in certain conditions when player is in the middle of desert and types special command. Multiply that stuff times thousand, and here you go, excessive processing for OPC, while in fact you should lazy-load: that is load that kind of records only when they are first accessed (and then kept in memory as you've executed query already). Very important note though: the actual plugin call to execute query is the heaviest part of this, as the actual query is only fraction of a second, so you have to balance between large amount of data vs small but frequent loadings.
Goddamn, that's a wall of text. I'm no expert, just a few thoughts (and I changed that a few times for no specific reason so there might be some weird jumping between thoughts)