WCA Statistics Website

[antoineccantin]

Nothing on the site seems to work for me... (except for the stats on worldcubeassociation.com)

[ardi4nto]

Is this project still alive?

[Henrik]

I guess it died

[Sebastien]

They are having problems with the webspace.

[jonlin]

If they fix it, put Youngest/oldest solver/blindfold solver
I want my name on there

[Mike Hughey]

If they fix it, put Youngest/oldest solver/blindfold solver
I want my name on there

You don't need the statistics website for that - you're already on the statistics page of the main site:
http://www.worldcubeassociation.org/...statistics.php

[macky]

We'll get stats.cubing.net working again, but it'll take some time to do it well. The hosting service shut down the database because some queries were taking too long, for reasons I don't understand. We have to improve the queries (probably using many pre-computed tables) and test them before we can put them back online without risking getting Lucas in trouble.

It's too big of a project for me alone; I need to do math. If you have knowledge about MySQL/php and would like to lead the project or help maintain it, let me know at Gmail account smakisumi.

As a note, all the code is on github: https://github.com/cubing/stats

[Stefan]

I don't have my new system set up yet and I'm not familiar with git(hub), but here's my code suggestion for most_solve_hours.php, maybe someone else wants to integrate it into the repository (if not, I'll do it, but I don't know when). Or maybe it can just serve as an example for how to precompute and use stuff later. I tested this code when I wrote it recently, and it did speed up the very slow original query a lot.

Do this once after every update/import of the database:

DROP TABLE HelperMostSolveHours;

CREATE TABLE HelperMostSolveHours
SELECT   personId, personCountryId, continentId, year,
       (sum( if(value1>0,value1,0) ) +
        sum( if(value2>0,value2,0) ) +
        sum( if(value3>0,value3,0) ) +
        sum( if(value4>0,value4,0) ) +
        sum( if(value5>0,value5,0) ))/100/60/60 hours,
        personName
FROM     Results
       JOIN Events      ON Events.id = eventId
       JOIN Competitions ON Results.competitionId = Competitions.id
       JOIN Countries    ON Results.countryId = Countries.id
WHERE    format = 'time'
GROUP BY personId, Results.countryId, year;

Use this (well, with the dynamic conditions instead of hardcoded year<=2008 etc, of course) in the viewer script:

SELECT   sum(hours) hours, personId, personName, personCountryId
FROM     HelperMostSolveHours
WHERE    continentId = '_Europe'
 AND    year <= 2008
GROUP BY personId
ORDER BY hours DESC
LIMIT    100;

Some timings on my PC:

Original query: 15-17 seconds

Original query simply without the "LIMIT 100": 1.0-1.7 seconds
(Very interesting, somehow telling it to produce less made it a lot slower... apparently the overhead of trying to do less work actually made it a lot *more* work.)

Precomputation: 3 seconds
Query using the precomputed table: 0.1-0.2 seconds

[Noahaha]

How many people who have never had WRs would have at some point had one if Feliks was never a speedcuber?