On Sat, Dec 30, 2017 at 8:55 PM, Huji Lee huji.huji@gmail.com wrote:
What is the proper strategy for temporary (physical or in-memory) tables on Toolforge?
Allowing temp tables on the new Wiki Replica servers was investigated in https://phabricator.wikimedia.org/T179628. The resulting decision was that `CREATE TEMPORARY TABLE...` can cause too many issues with replication to be allowed at this time.
For this particular query, I think you could simulate having a temporary table by "joining" in your application by selecting counts of revision_userindex table rows using batches of (log_user, log_timestamp) values. It would depend on the size of the inner query dataset whether you are better off trying to cache all of the results in your application memory or instead using two database connections and only reading in a "reasonable" number of results at a time (e.g. 1000).
Bryan