Hi Tyler, Tony, Dan, Mike and all others involved here.
Michael assigned this this issue to me so that I can help out or complete things to make sure we don't miss out on the excellent discussion and coding that has gone on here.
I've been looking over this issue and your patch Tyler at https://github.com/tbannister/moodle/compare/master...MDL-30643_master-log-speed for the past day.
I like the look of the way things have shaped up. The use of temp tables appears really smart and seems well suited to the present situation.
While I agree with Petr's earlier comments about the log systems as well as stats needing a general overhaul I think certainly that if we get this code tidied up a little and verify its effects across the 4 DB's we support in core then it should land.
As mentioned I've already been looking over this code, and have talked to Eloy about this issue and the proposed solution as he really is our database expert.
As I see it at the moment there are three things that need to happen before we can get this issue up for integration and eventually into core.
1. Review and tidy up the code.
There is one large change I believe needs to be made that came from discussions with Eloy. We think it would be best to create temp tables using a method similar to that used when creating the temp backup tables.
Rather than construct the temp tables in code as is happening now within stats_temp_table_create we think it is best those temporary tables are created from templates.
In the case of backup that is handled through backup_controller::create_temptable_from_real_table. It then creates temp tables from the real tables that have been set up as templates e.g. backup_ids_template and backup_files_template.
Taking this approach may not be quite so quick but having those tables defined in a standard way helps make them more visible and manageable in the future.
In this case things could also be done a little differently. The stats_daily and stats_user_daily tables are nearly identical to the temp tables you are creating. If we get indexes sorted then we could easily use those as the templates for their temp table equivalents.
The temp log tables are a different story, really they would require a separate template table. Perhaps called stats_log_template.
Other than that I havn't spotted anything requiring significant changes. Naming of tables and functions is perhaps the only other thing worth mentioning. Naming the temp tables with "_temp" as a prefix worth doing and a more consistent scheme to temp tables already being used in Moodle.
2. Guarantee results through unit tests with a crafted dataset.
In discussing this with Eloy we also felt this would be something that would be not only essential to getting this integrated but also highly beneficial in ensuring that things were being generated with the same accuracy that they were prior to this patch. As someone mentioned above this issue should focus on performance. Any efforts to improve stats generation should be undertaken in another issue.
With unit tests we would have an easy means of testing any and all changes, both as part of this issue and in any future issues concerning stats generation.
Certainly it will help prove any changes being made in queries.
3. Ensure operation and performance on all supported databases.
This is really just about testing. Certainly having the unit tests above will help us in this, but definitely it is still worthwhile running the stats generation on large datasets in all 4 databases and monitoring performance to ensure we only move performance forwards and not backwards in all systems.
This should also be reflected in the test instruction for this issue when it does get integrated so perhaps creating a means of generating the required data would help.
Really the first question I have is for Tony, whether you would like continue developing this solution or whether you would like me to take over and finish off development, namely the tasks noted above?
If you would like to continue development then I will peer-review the code you've got properly, give detailed feedback and help you out in any other way I can.
If I take over development then I will review and make changes as I go, of course I will make any changes on top of existing commits kudo's aren't lost.
I'm quite happy either way.