Every table that had user
type fields has a similar actor
field. That field is the id to find in the actor table
Example: ar_user and ar_user_text from the archive table would be replaced with a join on the actor table using the ar_actor field as actor_id or a separate query, which can be faster in some cases, to obtain the needed actor_name associated with a given archive action.
The list of actor fields follows:
What about a quick solution if the refactor is too time consuming at the moment
Tables affected by the actor change also have a _compat view (for example archive_compat) which have the actor table joins already present in the view's code and will present just like the affected table did before the changes took place in the MediaWiki codebase. The _compat views will suffer a performance penalty because of those same joins. In some cases, where performance isn't the most important factor, this may be the quickest way to fix a tool or application. Use of the _compat views should not be considered a permanent solution for a long lived tool. These views will very likely be removed in the future.
The actor table seems really slow--so does comment
The views for actor and comment are burdened by subqueries against 8 other tables in order to ensure that only the correct rows are available. This is being worked on here T215445
, but since that's work toward a long-term solution, WMCS has put up a set of sub-views of these tables to make things faster for individual queries (phab:T224850
). Each subview only makes a subquery against a single table. This way, if you are looking for an actor
row from the logging
table, then you can query against the actor_logging
view. This view will be more efficient for that case. The actor_logging
view will, naturally, not have any rows available that might be referenced instead in the revision
table or the archive
SELECT * FROM logging_userindex
JOIN actor_logging ON actor_id = log_actor
WHERE actor_name = 'MusikAnimal'
The specialized views that can be used in this case are:
Advanced use cases of specialized views
There are other more clever ways to exploit the fact that these views include a single subquery against the external table in the second part of the name to remove joins or where clauses from your own queries, but we suggest experimenting to ensure you are getting the same results if you try this. An example of this is, if you were just trying to see if user "Foo" had ever made edits to a wiki, you could reasonably get that from SELECT 1 FROM actor_revision WHERE actor_name = 'Foo' because any rows with that actor name would be rows that are visible in the revision table. This would likely be faster than a join query for that particular piece of information.