DARK HUNTER wrote:Maybe you can add Country and Mod statistics: Like what what mods and on what maps do players from this country usually play on. And how many players are from that country.
Or for mods: What are the top maps for that mod and and what countries and clans usually play that mod.
This was the original design, but I had to drop country and mod statistics because of performance problems.
At the beginning, there was only one table without index, containing the following fields :
- current date
- player name
- player country
- clan
- server
- score
- mod
- map
However, computing histograms from this structure has proven excessively long to perform, even though the SQL requests were optimized.
For instance, computing the histogram of mods for a particular player reads « SELECT count(*),mod FROM table WHERE playername='...' GROUP BY mod LIMIT 8 »
As a result, I duplicated this table three times, each with a different index field (player, clan and server). This is called vertical partitionning.
Following this, the site was published in the forum and the SQL server began to overload.
I've had to shut down the access to the site. Yet 2 min later, SQL requests were still being processed.
At last, I ended up implementing three more tables for caching. They look like this :
- player (primary key)
- timestamp
- histogram1
- histogram2
- serverlist
...
This is used to cache requests : if the timestamp is more recent than 5mins ago then it is used, otherwise the histograms are computed and cached.
In addition to all this, the cache size of MySQL InnoDB tables was increased to 300MB so that most of the db stays in RAM (its ~ 400MB).
If in spite of everything you manage to overload the server, then I'm out of ideas 
Of course, it would be interesting to add more stats and i'm open to suggestions but they need to come with the corresponding kickass computation algorithms 
