Best way to use a MySQL Database

Hello Developers,

I am busy with a plugin that saves the total online time, kills, deaths etc.. and all that kind of stats. I want to use a MySQL database for it to store the data on it. It must be a little bit real time because i want to show that stats on an website of my server. The server makes with all stats 70+- querys per minute * users (yes, querys are optimized. I do MySQL Querys already 6 years..)

So, what is the best way to store this data? Using a threaded pooling datasource connection? Or use a library that already do this?

 

Bump

 

You have done MySQL Queries for 6 years but don't know the plural of "Query"? D:

There's no excuse for making anywhere near 70 queries/minute. Store your queries in some kind of an offline list, and then every so often run a thread to update the database. This doesn't need to be every minute, it should be more like 5 or 10.

 

I suggest you check out my plugin, bukkitstats. It's great for storing stats in a database, just like your trying to do. To reduce the plugin->mysql lag, it does plugin->php api->mysql so i doesn't have to wait for a response. This makes it great for mysql databases that aren't on the same machine and when making a LOT of queries.

 

Well, firing 70 queries a minute at a MySQL sever won't really bother it that much, however it all depends on how you have set the table up and how the queries are formed. Generally, you would in these kinda cases be able lower the amount queries actually needed by combining a few. Such as instead of doing a

Code:

"SELECT blocksBroken FROM playerStats WHERE player = '" + player.getName() + "'"
"UPDATE playerStats SET blocksBroken = " + (Integer.parseInt(res.get("blockBroken")) + 1) + " WHERE player = '"player.getName()"'"

That could be optimized as example below. This does the exact same, and yet saves you a select query and resources on the client side for parsing the result and such.

Code:

"UPDATE playerStats SET blocksBroken = blocksBroken + 1 WHERE player = '" + player.getName() + "'"