Quote:
Originally Posted by eraserhead
Thanks, uploading your file now :-)
And yeah, it's pretty slow.
|
Ok, I took a closer look at player_rankings.php and I believe I can improve its performance significantly.
The attached file should be renamed to player_ranking2.php and uploaded. If it seems to work properly, you can just rename it to player_ranking.php instead, replacing the default file.
As I haven't had a TF2 server for several years now, I have no way to test it, so it's at your own risk. I only promise I haven't added anything that will harm your data.
Technical explanation for possible improvement:
Every query to the database takes times - a lot of time when there are no indexes on the database table and the table is big. The page previously made a scan for the relevant players for the page. For each player, it would then have two additional queries, one fairly simple and another one which probably takes ages. This results in a total number of queries for the ranking at 1+1+2*players-per-page, which, on Eraserhead's server is 50 per default = 102 queries per page.
The simple query did not change per player search, so I moved it outside the loop, instead caching the results for each player. This brings us down to 52 queries (Although the ones we got rid of were lightweight, and depending on the DB settings, may already have been cached).
The heavy query for each player was calculating their ranking. The ranking in this plugin is done by checking the database for the number of players who has the same or a larger number of points as the player himself. This means a total table scan for every player since there are no indexes on points as discussed in my previous post. In order to combat this, one scan is now performed to get the rank of the first player on the page, and the rest of the players' offset are then calculated based on this information. An additional scan is performed to get the rank of the last player (As more players with the same number of points may be on the next page).
This means that we're down to a flat number of queries, namely 1+1+1+1+1 = 5 queries total, of which 3 are heavy. What this means is that worse performance will be had if the players-per-page is set at 1, resulting in 5 queries instead of 4 as before. In all other cases, the performance should be better. For the (what I assume is default) players-per-page at 50, this means about a 94% reduction in the number of "heavy" queries, which should bring about a vast performance increase for the player rankings page (Provided your database table is big like Eraserhead's)