Remote MySQL? Efficient?

Hey guys, I’m using a server from Xenon to test some addons, and most of them use mysql. My question is, how reliable would it be to connect to my database on my webhost from the server? Will I get a huge delay? I need it to query every time a player joins and leaves, and I’m afraid it might not get the data in time and fail or something. Thanks.

MySQL itself is incredibly fast with queries in the millisecond range, and tcp will make sure no data is lost on the way. The only question is how fast you can transfer between the two servers.

It’s fine as long as you set up the system to be callback based.
Using the threaded MySQL module, you can send query and wait for the result.
While the result is pending, the thing being affected by the result can be put in a state of waiting.

For example, if a player is joining, you’d set them to a spectator team until the query has returned a result.
If they are banned, kick them (gatekeeper recommended).
For leaving, you don’t need to wait at all.

If you need it for an interface, cache the data on the client. If, for whatever reason, you have a large amount of data, you should create a ‘dynamic scrolling’ system.

When the user opens the interface, they send the graphical size of the list (number of entries shown at once).
The first few items (how many can be seen) are sent to the client, as well as the total number of entries. Now the scrollbar can be laid out.
As the user scrolls, calculate their ‘scrolling velocity’. When they pause on a view for more than 50ms, send a query to the server asking for the items in that view. Every 100ms, calculate their scrolling velocity and send a query to the server of a wide area where they might land.
For the best results: place a search interface (single line or more advanced) at the top to filter the results.

To communicate the results to the client, I recommend datastream (one ‘datastream stream’ per view (so if you get 3 views at once to allow for scrolling velocity, send them separately)).
Have the format like:
[lua]
stream = {
[57] = “omg”,
[58] = “what”,
}
[/lua]
So that this can be done:
[lua]
for k,v in pairs(stream) do
results_cache[k] = v
end
[/lua]
This will allow the client to keep their results while having new ones appended appropriately.
Remember to NOT clear the result cache when they change the search string!
Have the server send the search query that returned the results in the current stream back to the client so it can append it to search_results[search_string].
For fast responses, you should do client-side searching of the result_cache.
Remember that search string queries should be dealt with just like when viewing the entire thing (dynamic scrolling).

Have fun!

May I ask what Gatekeeper is and what it does? Thanks for that great response Deco :slight_smile:

Search is your friend :eng101:
http://www.facepunch.com/showthread.php?t=695636

Yeah I searched it a while ago, I don’t see why I’d want it :stuck_out_tongue: Thanks anyways aualin :smiley:

You can query their account data before they are in the fully server and kick them at any point, whether they are fully loaded or not.

This part is very important. If you were to use an un-threaded MySQL module, the server will not respond until it receives the result of the query, making it appear to crash.