Making a player stats page | Converting UUIDs faster

Discussion in 'Programming' started by Vengea, May 14, 2015.

  1. Vengea

    Supporter

    Hey! I'm in the progress of making a page were you can see all the players and their stats from our server. The database table which includes all the stats does not save usernames but only UUIDs. The way I did it is way to slow using the Mojang API, so anyone here that can help me out? :)

    Here is my php code:
    (Without the connection code)
    Code (Text):

    mysql_select_db($database_statskobling, $statskobling);
    $query_Stats = "SELECT UUID, Experience, Premium from General ORDER BY Experience DESC";
    $Stats = mysql_query($query_Stats, $statskobling) or die(mysql_error());
    $row_Stats = mysql_fetch_assoc($Stats);
    $totalRows_Stats = mysql_num_rows($Stats);

     
    Here is the HTML code.
    Code (Text):

    <body>
    <table border="1">
      <tr>
        <td>Avatar</td>
        <td>Name</td>
        <td>Experience</td>
        <td>Premium</td>
      </tr>
      <?php do { ?>
        <tr>
    <?php

    $json_response = file_get_contents("https://sessionserver.mojang.com/session/minecraft/profile/" . $row_Stats['UUID']);
    $username = json_decode($json_response);

    echo '
    <td><img style="width:36px;height:36px;" src="//cravatar.eu/helmavatar/' .$username->{'name'}.'"/></td>
    <td>' .$username->{'name'}. '</td>
    ';
    ?>
          <td><?php echo $row_Stats['Experience']; ?></td>
         <td>
    <?php
    if ($row_Stats['Premium'] == 1) {
        echo "Premium";
    } else {
        echo "Not premium";
    } ?>
    </td>
        </tr>
        <?php } while ($row_Stats = mysql_fetch_assoc($Stats)); ?>
    </table>
    </body>
    </html>
     
    Here is how the database table is setup:
    [​IMG]

    You can check it out here:
    http://pvpheroes.no/stats.php

    As you can see, the page is really slow, so how can I make things go faster?
    Should I ask our developer to store usernames in the database?


    Yes, I do know about caching files, but still, the process of making the cache file is too slow I think.
    You can check out the toplist I made, which is stored like a cache file :)
    http://pvpheroes.no/
     
  2. Cache the usernames for x amount of time. A day or so would be good.
     
  3. Vengea

    Supporter

    Yeah, but the time when you have to regenerate the cache, it takes too long, thats what Im trying to get faster :)
     
  4. why not store the last logged in username onJoin, it wouldn't be correct for any players that do not join after name changes, but you shouldn't be horribly interested in those either. It is what I do to make lookup faster. With a [uuid]| [username]|[stat1]|[stat2]|[statX] etc.. This is almost exactly how my DB is setup for stat and info handling. I just check the value of the DB on login, if the username does match the resulting username with the UUID, then it just gets changed.
     
    • Agree Agree x 2
  5. Hmm, Personally, I'd likely store their current usernames as well. For caching purposes, you can use the timestamp of when their name was last changed. If it was changed less than 30 days ago, you know you don't have to recheck for a new name until that expires. Otherwise, check every 24hrs. You could run a cron script to automatically do this every day at midnight, so it won't effect your visitors. It will be a task that the server calls on its own (thus it wouldn't matter if it was a pinch slow, since the visitors would never have to experience it).

    Also, to do this you'd need mojangs Name API. It will return the players name history, along with the timestamps in which they were changed. :) Just grab the latest one. Oh, and the time stamps are in milliseconds.

    API: https://api.mojang.com/user/profiles/bf2b4774bd8e4eabb4506536e5dde1d2/names

    Ultimately drop the whole caching the last changed name date idea and just run a cron job to re-collect the names at the end of every day from your current code.
     
  6. Vengea

    Supporter

    Hey! Our developer added a new row in the database which stores the players username, since our server was working flawlessly with our database, this wouldnt effect performance :)

    SeiRruf, it seems like you know about alot of stuff, so can I ask something?

    Oh, was that a yes? Okay;

    On the old dedicated server I had, my caching was working flawlessly, but after I moved the files over to the new dedicated server, the caching wont work for some reason. Is there any settings in Apache that I need to change? I have been searching on google, but couldnt find what I was looking for :)
     
  7. Yeah sure!!

    ..Yes!, go on..

    Hmm, this could indeed vary depending on how you coded everything. What functions you are using, or libraries, etc. Or were you using an apache module to cache, and not code at all? But to give you a true answer, I'd need to know a few things. What errors it is tossing (if any), how is it misbehaving, exactly?
     
  8. Vengea

    Supporter

    I just removed the cache since we are not using the Mojang API anymore, everything goes Sonic fast :D
     
  9. Excellent! Happy to hear :) Cache is always a smart though if you have heavy traffic though. It helps take some weight off of what ever API you're using.
     
  10. what if this happened:
    they leave -> they change their name -> they go on a different server
     
  11. Its impossible to have a completely updated list. Youd have to be querying every single name in your database constantly ensuring it hasnt changed.

    My question is if this is just for your server, why do you care? I have a webapp and a public api that you are welcome to use http://mcplayerindex.com to lookup player accounts