You really need to split up that query.
When a player's money changes, it doesn't have any effect on weapondata, so why update weapondata along with it?
Have small queries that only update what needs to be updated.
You can write many small functions to update data in the script and save it to mysql at the same time.
Like this one:
PHP Code:
// This function adds the given Money and Score values to the given player
Player_Reward(playerid, Money, Score)
{
new Query[128];
// Add the given Money and Score to the player's account
pData[playerid][PlayerMoney] = pData[playerid][PlayerMoney] + Money;
pData[playerid][PlayerScore] = pData[playerid][PlayerScore] + Score;
// Update money and score for this player in the player's account in MySQL
mysql_format(SQL_db, Query, sizeof(Query), "UPDATE playerdata SET Money = '%i', Score = '%i' WHERE ID = '%i'", pData[playerid][PlayerMoney], pData[playerid][PlayerScore], pData[playerid][SQLID]);
mysql_tquery(SQL_db, Query, "", "");
return 1;
}
Big queries like yours are slowing mysql down alot.
Mysql needs to analyze your big query, parse each column-name, search for it, update data and do some internal sorting stuff on the data to allow faster searching (called indexing).
With big queries, this takes alot more time compared to sending a few small ones.
You're not updating ALL fields every second or so anyways.
Also, you seem to have multiple columns for weapondata.
You need to normalize your tables (split them up) and save each weapon in a separate row.
General rule is if your column-names have numbers in them, you're doing it wrong.
Also, don't restrict your script to saving everything at once upon disconnecting.
If players keep playing for hours and make alot of progress, and your server goes down unexpectedly (server crash, power failure, DDOS attack, rare bug in the script, ...), all that data would be gone and your players will get mad because they lost hours of playtime.
Save whatever changes when it changes, simple as that.
Player earns money -> save the money.
Player earns scorepoints -> save the scorepoints.
Player buys a house -> update housedata (for that house only, don't update ALL houses).
Don't keep it all gathered in memory and just save when the player leaves, it will lead to dataloss and furious players.
awesome information Vince!