Poor performance

[Diego Heras]

When there are many people playing at the same time the page http://tools.wmflabs.org/wikidata-game/api.php?user=XXXX&action=get_candidate&table=genderless_people don't load. It's annoying because you have to wait for a long time and disrupts the game.

At this time que query only return one item, i. e.

{"status":"OK","data":{"id":"907575","item":"499783","status":null,"user":null,"random":"0.643104","timestamp":null}}

Instead of returning just one item you may return 5 or more and so you reduce the number of requests.

[Diego Heras]

I saw the real problem. I have only one language, "gl", in the white list and the checkbox exclusive whitelist selected. I think I've finished all the questions of the game "Gender" and the following loop is infinite, so the page does not load.

if ( $action == 'get_candidate' ) {

    $uid = getUserID($user) ;
//  $out['log'][] = "$user => $uid" ;

    $table = $db->real_escape_string ( trim ( get_request ( 'table' , '' ) ) ) ;
//$test = ( $table == 'genderless_people' and get_request('test','') == '1' ) ;
    while ( 1 ) {
        $r = rand() / getrandmax() ;
//      $sql = "select * from $table where status is null order by rand() limit 1" ;
        $sql = "SELECT * FROM $table WHERE status IS NULL AND random >= $r ORDER BY random LIMIT 1" ;
        if(!$result = $db->query($sql)) die('11 There was an error running the query [' . $db->error . '] '.$sql);
        $x = $result->fetch_object() ;
        if ( !checkSpecialConditions ( $table , $x ) ) continue ;
        if ( !checkUserConditions ( $table , $x ) ) continue ;
        $out['data'] = $x ;
        break ;
    }

I don't know the content of the table "genderless_people" but I think it's possible to add one column called "languages" with a list of all languages separated by comma. An change the query to something like that:

$sql = "SELECT * FROM $table WHERE status IS NULL AND random >= $r AND (language LIKE '%en%' OR language LIKE '%fr%' OR ...) ORDER BY random LIMIT 1" ;

[Magnus Manske]

That's certainly possible, however, it would not update if languages are added/removed on Wikidata. I guess one (cheap) way would be to count how often the while loop ran, and warn the user if candidates get low.

[TMg_]

Please note that LIKE queries as shown above with a placeholder as the first character can not use any indexes. Querying by language should be done with ... = 'en'.

[Diego Heras]

@magnusmanske You have to solve this problem. It's almost impossible to play during day. I prefer to see a warning message than the game never loads...

[Waldir Pimenta]

Issue #60 was marked as a duplicate of this issue.

[Svavar Kjarrval]

It's probably better to use an IN SQL statement instead of a LIKE one, and to have a special table connecting each WikiData item to a language. Then lookups should be fairly fast and more reliable.

The current setup causes a few problems if there aren't enough items left for "my" language(s) to randomly show up when the game queries the database. That way I'll never reliably know if I've done every item for my language(s) or if there are some items left which the random function never digs up.

[MrLinkinPark333]

For me, the only game that loads is Image. The rest doesn't work.

[Magnus Manske]

Rather than fixing issued in this codebase, I'd prefer to fix or port games on/to the Distributed Game, here: https://tools.wmflabs.org/wikidata-game/distributed/ This already has batch loading, low-candidate warning, etc.