Re: Query using "ref" instead of "range" access

[Rick James]

Thanks for the timing and the bug link. I learned something.

The query is Order(n*n), so the real optimization is to shrink that to Order(n*log(n)) or better.

If 2 sec is not adequate, there may be complex ways (I have not thought through the details):

Plan A: Summary table that is maintained whenever a game is played. It would be indexed by a pair of player ids. It would be updated (twice?) to change the relative info about that pair of players. Then the SELECT could run against this table, thereby avoiding the subquery. Possibly a trigger could be used to do the update. (INSERTs would be slower, but probably tolerable.)

Plan B: Instead if trying to do the SELECT in a single statement, create a TEMPORARY TABLE. (I haven't thought through what would go in it.) Possibly add an INDEX to it (hence, the log(n)). Then SELECT from the tmp table.