Last modified: 2014-07-23 07:36:49 UTC
I would like to use the NavigationTiming tables to make a dashboard of image loading speeds by filtering on the action, anon and namespace fields (the patch is at https://gerrit.wikimedia.org/r/#/c/148021 ) which will probably be slow without indexes on the relevant columns. (I don't need it, but in general event_isHttps also seems like a good candidate for an index.)
Adding sean (on ops) to ticket as normally he does the db management of this db. I believe analytics team does not have permits to add indexes.
The eventlogging slaves (analytics-store and s1-analytics-slave) already have indexes on (wiki, timestamp) for all tables, plus the optimizer chooses to automatically index the derived tables generated by the subqueries. The result isn't terrible: ~4m for wiki = 'enwiki' and ~1m for dewiki. How frequently will the queries run?
Once a day per wiki.
event_action is probably not useful since we are filtering on view events, which are the majority, so it's unlikely that index would end up being used. event_namespaceId on the other hand should have pretty high selectivity.