General top 100 leaderboard list

I know eq2mission has a leaderboard, but I would like to include one on my guild website, but I can't figure out which item to key on for a quicker lookup. I'd like to have the top 100, but the query I have is very slow even with just a limit of 10.

My query is:

/jml/get/eq2/character/?c:show=name.first,locationdata.world,quests.complete&c:limit=25&c:sort=quests.complete:-1

What do I need to get the results more quickly.

Thanks for the help.

 

The query looks fine.  The issue is that quests.complete hasn't been identified as something that needs an index yet so it's having to look through over 900,000 characters to answer the query.

If you add &c:explain=1  to the end of your query, it will show you it's plan for how it's going to run it.

 

When I put this at the end of my query I got a 404.

Hmm, there must be something different being done at the eq2misssion leaderboard page, as it loads the top 100 faster than I can get the top 10 to load. Maybe it's that the info there is being stored in a local database, as I noticed that the quest completed data hasn't been updated for any of the characters on the list for quite some time.

 

Here's a link with &c:explain=1 added to your query:

census.daybreakgames.com/jml/get/eq2/cha...:-1&c:explain=1

It does take a while to run though and might time out. It ran when I made the first post but is timing out every time I try it now.

And you are correct, eq2mission pulls all the data into their local database and then runs thier queries from there.  That's how they are able to do leaderboards that wouldn't return from the REST API in a timely fashion or at all in some cases.

 

I now get the timeout for that query whether I use the one you linked or put it in myself. I guess for now I'll stick with my guild leaderboard instead of including a world leaderboard for the time being. Thank you for the help.

 

EQ2Mission is farming/caching the entire player API data and then doing local searches.

I personally think the best approach for availability/performance vs. accuracy of data is to run leaderboards queries hourly or daily as a scheduled task and then output a nice HTML page. This would also allow you to leave out the performance-robbing c:sort from the query and do that locally.

I don't know any way to do even the simplest leaderboards with real-time queries.

 

Setting up a scheduled task is not anything I've done before so I don't even know where to begin to do something like that. I guess I'll look into that when I'm done with the rest of what I want to do , as the world leaderboard idea is an extra for my guild website.

Thanks.

 

Not sure what you're using now but have you looked at Roster Master?  It uses the new data feeds and provides a good bit of detail.  Roster Master Stand Alone even provides a bit of leaderboard functionality built in as well as a mini-character viewer.  You can see a live demo of it here.  It has a few features that aren't included in the demo as well.  Lantis and Dark Grue did a great job on Roster Master Stand Alone and Roster Master For DragonFly.

 

I'm using a self writen site. I have looked at Roster Master and I have it installed, but I haven't the styling for it to match my website, besides the fact that I do like to write my own code when I can

 

Doing guild leaderboards is fairly easy once you have a local database containing complete guild roster data.  For example, in RosterMaster the following code gets you a top-5 questers, and displays it in a table (I removed the actual table code, as these forums were sending the HTML tags to the browser...):

Code:

$query = "SELECT rm.name, rm.quests FROM {$config['prefix']}$db->rostertable rm ORDER BY rm.quests DESC LIMIT 5 ";  if(!($result = $db->sql_query($query))) { error_message(_RML_ERR_READDB,$query); }  while($row = $db->sql_fetchrow($result)) { $formatted = number_format($row['quests']); echo $formatted; }  $db->sql_freeresult($result);  

(Excuse the poor formatting, formatting is kinda limited on these forums)

Getting the whole guild roster data takes about 2 secs at most, and further leaderboard queries run on the local database are instantaneous.  What you could do is install RosterMaster StandAlone on your server (no need to expose it to your users) so it takes care of pulling the guild roster data and storing it in the local database (use a cron job to ensure it gets refreshed every few hours - I explain the setup in the documentation), then write your own code to query the RosterMaster local database and display results the way you want.

(and BTW RosterMaster's display is quite customizable - just edit the CSS <img src="/station/images/smilies/3b63d1616c5dfcf29f8a7a031aaa7cad.gif" border="0" /> )

 

Most PHP installations are not set up in a way that you can run scheduled tasks on a specific time/hour of the day. So what most software like vBulletin does is on the index page of the forum, it has something like:

<img src="scheduled_tasks.php" width="1" height="1"><img src="scheduled_tasks.php" width="1" height="1" /><img src="crontasks.php" width="1" height="1" /> and then the scheduled_tasks.php script checks to see what time of day it is and if it's after a certain time, and today's task has NOT run, then it runs the task which could be a series of Data API queries which then store formatted data locally that can be easily displayed.

 

RosterMaster already has its own auto-update built in - it will refresh the database if a visitor hits the site and the database hasn't been refreshed in "x" hours (as configured in the installation settings).

Cron support is more common than it used to be however.  Cron was the only viable way LOTRoster could work (as it would take many minutes to pull data off Turbine's REST API), and I've seen a fairly decent installation base out there, all using Cron.  It's just not always very well documented.