The script about requests to recentchanges table has a bug. There are many calls to RC without any parameters (RC issues a redirect to default but maybe it doesn't sometimes) and when the argument of hidecategorization=0 is not set, the category changes are not shown. So we have a trinary of: 1- hidecategorization=0 2- hidecategorization=1 3- nothing in parameters. 2 and 3 won't show them but 1 does. So you need to check for lack of that. For example I ran the query on kowiki:

SELECT
  uri_host,
  sum(if(instr(uri_query, "hidecategorization=0") = 0, 1, 0)) as hidecategorization,
  sum(if(instr(uri_query, "hidecategorization=0") > 0, 1, 0)) as showcategorization2
FROM wmf.webrequest
  WHERE webrequest_source = "text" AND year = 2025 AND month = 6 AND day = 20 AND hour = 10
  AND uri_host = "ko.wikipedia.org"
  AND is_pageview = true
  AND x_analytics_map['special'] = "Recentchanges"
GROUP BY uri_host;

You can play with it, 1 is zero 2 is 101 hits and 3 is 17. So the showing of category changes is actually zero not 17 (unlike what the script says)

Also worth looking into ideas brought up in T307328#10715066 specially the first one.

In T396489#10947696, @Ladsgroup wrote:

You can play with it, 1 is zero 2 is 101 hits and 3 is 17. So the showing of category changes is actually zero not 17 (unlike what the script says)

i.e. I do think that feature is barely used.

In T396489#10947696, @Ladsgroup wrote:

The script about requests to recentchanges table has a bug. There are many calls to RC without any parameters (RC issues a redirect to default but maybe it doesn't sometimes) and when the argument of hidecategorization=0 is not set, the category changes are not shown. So we have a trinary of: 1- hidecategorization=0 2- hidecategorization=1 3- nothing in parameters. 2 and 3 won't show them but 1 does. So you need to check for lack of that. For example I ran the query on kowiki:

SELECT
  uri_host,
  sum(if(instr(uri_query, "hidecategorization=0") = 0, 1, 0)) as hidecategorization,
  sum(if(instr(uri_query, "hidecategorization=0") > 0, 1, 0)) as showcategorization2
FROM wmf.webrequest
  WHERE webrequest_source = "text" AND year = 2025 AND month = 6 AND day = 20 AND hour = 10
  AND uri_host = "ko.wikipedia.org"
  AND is_pageview = true
  AND x_analytics_map['special'] = "Recentchanges"
GROUP BY uri_host;

You can play with it, 1 is zero 2 is 101 hits and 3 is 17. So the showing of category changes is actually zero not 17 (unlike what the script says)

Thank you for that; I wasn't aware that there were non-redirect situations and so I though is_pageview would filter out any of the param-free hits since it limits to status=200

I'm changing to

sum(if(instr(uri_query, "hidecategorization=0") > 0, 1, 0)) as showcategorization

which should should now include only that 1st state; again, thanks!

In T396489#10947696, @Ladsgroup wrote:

[...] when the argument of hidecategorization=0 is not set, the category changes are not shown. [...]

I just double checked this on enwiki and that does not line up with what you are describing; When I select category changes and deselect the other types, I get the following url:
https://en.wikipedia.org/wiki/Special:RecentChanges?hidebots=1&hidepageedits=1&hidenewpages=1&hideWikibase=1&hidelog=1&hidenewuserlog=1&limit=50&days=0.5&enhanced=1&urlversion=2

which does not have a hidecategorization parameter. I am seeing the category changes:

That's a bug honestly. MediaWiki should not show categorization changes by default.

It looks like the redirect is happening for the js-enabled version only; the nojs web form version does not redirect. I'll amend my query to exclude views that have no query parameters so that the numbers within a row are at least comparable.

Okay, I've brought these learnings into a slightly larger notebook that is checking usage of all of the available parameters because that is a thing we are interested in:
https://gitlab.wikimedia.org/jsn/notebooks/-/blob/main/webrequest-rc_filter_usage.py

In T396489#10947702, @Ladsgroup wrote:

Also worth looking into ideas brought up in T307328#10715066 specially the first one.

Agree!

In T307328#10715066, @matej_suchanek wrote:

[...]

Redundancy of rc_new, rc_type and rc_source

MariaDB [wikidatawiki_p]> SELECT DISTINCT rc_new, rc_type, rc_source FROM recentchanges;
+--------+---------+---------------+
| rc_new | rc_type | rc_source     |
+--------+---------+---------------+
|      0 |       3 | mw.log        |
|      0 |       0 | mw.edit       |
|      1 |       1 | mw.new        |
|      0 |       6 | mw.categorize |
|      0 |       5 | wb            |
+--------+---------+---------------+

See also T36320#8440663, T244080 and T74157.
[...]

The recommendation is to keep rc_source and drop rc_new and rc_type. I see this as shared work between moderator tools and sre. We (moderator tools) would update lookups to replace rc_new & rc type to use rc_source. sre would modify the database afterwards.

In T396489#10950809, @Ladsgroup wrote:

P78713 (An Untitled Masterwork)

1	('frwiki', {'mw.edit': 766644, 'wb': 790923}) 1.0316691971762644
2	('arwiki', {'mw.edit': 321440, 'wb': 763293}) 2.3746049029367846
3	('eswiki', {'mw.edit': 515816, 'wb': 665724}) 1.290623012857298
4	('ruwiki', {'mw.edit': 498632, 'wb': 550427}) 1.103874199810682
5	('itwiki', {'mw.edit': 371579, 'wb': 512170}) 1.3783609945664315
6	('ukwiki', {'mw.edit': 149335, 'wb': 476468}) 3.190598319215187
7	('arzwiki', {'mw.edit': 372773, 'wb': 424782}) 1.1395192248365626
8	('plwiki', {'mw.edit': 191479, 'wb': 384598}) 2.0085649079011274
9	('cawiki', {'mw.edit': 110444, 'wb': 353002}) 3.196208033030314
10	('zhwiki', {'mw.edit': 389674, 'wb': 350039}) 0.8982867730461873
11	('kowiki', {'mw.edit': 214961, 'wb': 326289}) 1.5178985955591944
12	('nowiki', {'mw.edit': 44170, 'wb': 318408}) 7.208693683495585
13	('jawiki', {'mw.edit': 339167, 'wb': 318023}) 0.9376590293277353
14	('trwiki', {'mw.edit': 95043, 'wb': 295747}) 3.111717854023968
15	('svwiki', {'mw.edit': 152640, 'wb': 285560}) 1.8708071278825995
16	('euwiki', {'mw.edit': 27394, 'wb': 273159}) 9.971490107322772
17	('fawiki', {'mw.edit': 209369, 'wb': 272267}) 1.3004169671727905
18	('ptwiki', {'mw.edit': 143803, 'wb': 265419}) 1.845712537290599
19	('rowiki', {'mw.edit': 48112, 'wb': 239137}) 4.97042317924842
20	('srwiki', {'mw.edit': 143324, 'wb': 235977}) 1.646458374033658
21	('idwiki', {'mw.edit': 111068, 'wb': 227945}) 2.052301292901646
22	('huwiki', {'mw.edit': 70273, 'wb': 227307}) 3.23462780868897
23	('cswiki', {'mw.edit': 72716, 'wb': 227030}) 3.1221464326970683
24	('hewiki', {'mw.edit': 179019, 'wb': 208286}) 1.163485440092951
25	('nlwiki', {'mw.edit': 137928, 'wb': 206277}) 1.4955411519053419
26	('hywiki', {'mw.edit': 38306, 'wb': 187199}) 4.886936772307211
27	('ttwiki', {'mw.edit': 2411, 'wb': 176835}) 73.34508502695977
28	('fiwiki', {'mw.edit': 119381, 'wb': 168641}) 1.4126284752179996
29	('dawiki', {'mw.edit': 16460, 'wb': 163255}) 9.918286755771568
30	('cewiki', {'mw.edit': 3613, 'wb': 156935}) 43.436202601716026
31	('elwiki', {'mw.edit': 49603, 'wb': 152838}) 3.081224925911739
32	('eowiki', {'mw.edit': 19537, 'wb': 148286}) 7.590008701438297
33	('viwiki', {'mw.edit': 68114, 'wb': 147524}) 2.1658396218104943
34	('glwiki', {'mw.edit': 26123, 'wb': 143201}) 5.481797649580829
35	('bgwiki', {'mw.edit': 24598, 'wb': 133214}) 5.4156435482559555
36	('bewiki', {'mw.edit': 9790, 'wb': 131979}) 13.48100102145046
37	('cebwiki', {'mw.edit': 238852, 'wb': 128013}) 0.5359511329191299
38	('metawiki', {'mw.edit': 99025, 'wb': 126785}) 1.2803332491795
39	('specieswiki', {'mw.edit': 26582, 'wb': 117869}) 4.434165976976901
40	('azbwiki', {'mw.edit': 1063, 'wb': 117451}) 110.4901222953904
41	('cywiki', {'mw.edit': 67181, 'wb': 111262}) 1.6561527812923296
42	('azwiki', {'mw.edit': 27503, 'wb': 104802}) 3.81056612005963
43	('astwiki', {'mw.edit': 9552, 'wb': 104274}) 10.916457286432161
44	('urwiki', {'mw.edit': 94659, 'wb': 103290}) 1.091179919500523
45	('simplewiki', {'mw.edit': 58007, 'wb': 95614}) 1.6483183064112952
46	('slwiki', {'mw.edit': 13096, 'wb': 89652}) 6.8457544288332315
47	('shwiki', {'mw.edit': 8215, 'wb': 89314}) 10.872063298843578

Moderator tools should investigate (or bring in another team to help investigate) why some wikis have so many wikibase changes (type 5).
It's not wrong in itself, but to have so many is an indicator that something is wrong.
My quickly jotted notes from talking to @Ladsgroup:

• wikibase client is subscribing to things it shouldn't
• for a given wiki with lots of wb changes, look when the source is wb - look at page and match item / entry
• try to identify the cause:
  • it gets this because of x aspect
  • deprecating old aspect
  • subscribes to old aliases

@Ladsgroup proposed moving category changes to a new special page to prevent having to join a new category changes table against the recent changes table. There isn't really a use case for viewing category changes alongside all of the rest, which is why they are hidden by default. I think this proposal makes a lot of sense. This would be joint work that would involve

• adding the new table; writing category changes to recent changes + the new table for a period
• adding the new special page
• removing references to category changes from recentchanges / watchlist pages
• an open question is how would this impact watchlists?

In T396489#10951467, @jsn.sherman wrote:

@Ladsgroup proposed moving category changes to a new special page to prevent having to join a new category changes table against the recent changes table. There isn't really a use case for viewing category changes alongside all of the rest, which is why they are hidden by default. I think this proposal makes a lot of sense. This would be joint work that would involve

• adding the new table; writing category changes to recent changes + the new table for a period
• adding the new special page
• removing references to category changes from recentchanges / watchlist pages
• an open question is how would this impact watchlists?

This sounds like a great place to start. I'm curious what @OTichonova would think about the design of breaking out categories to a new special page?
I'd imagine that people would still want to be able to add category pages to watchlist, so maybe this is where we start introducing a cap on the watchlist or introducing multiple watchlists?

Thank you, @jsn.sherman and @Ladsgroup, for all of your work on this ticket! I have a few comments on some follow-up work/tickets.

adding the new special page

I would create two hypotheses for Mod Tools to work on this: one for design (whether to pursue a new design or use the current RC and Watchlist design) and one for engineering work

removing references to category changes from recentchanges / watchlist pages

Let's create a follow-up task for this and add it to the required hypothesis work.

an open question is how would this impact watchlist?

Let's create a spike for this. I'm not sure this would be a Mod Tools spike, a DB spike or an effort from both teams.

In T396489#10951806, @Kgraessle wrote:

In T396489#10951467, @jsn.sherman wrote:

@Ladsgroup proposed moving category changes to a new special page to prevent having to join a new category changes table against the recent changes table. There isn't really a use case for viewing category changes alongside all of the rest, which is why they are hidden by default. I think this proposal makes a lot of sense. This would be joint work that would involve

• adding the new table; writing category changes to recent changes + the new table for a period
• adding the new special page
• removing references to category changes from recentchanges / watchlist pages
• an open question is how would this impact watchlists?

This sounds like a great place to start. I'm curious what @OTichonova would think about the design of breaking out categories to a new special page?
I'd imagine that people would still want to be able to add category pages to watchlist, so maybe this is where we start introducing a cap on the watchlist or introducing multiple watchlists?

Yes, and @Ladsgroup has mentioned a number of additional watchlist related performance issues:

• identifying watchlists attached to large bot accounts and clearing them out (after reaching out to their owners)
• revisit including talk pages when watching; this doubles the row count for a behavior that might not even be desirable
• normalize watch page targets to link target table to increase perf

When I think about multiple custom watchlists as a feature, I really begin to think that watchlists should just be restarted from scratch. I think this is all definitely something for @JWheeler-WMF and friends to consider for commtech's upcoming work in this area.

In T396489#10951832, @Scardenasmolinar wrote:

Thank you, @jsn.sherman and @Ladsgroup, for all of your work on this ticket! I have a few comments on some follow-up work/tickets.

adding the new special page

I would create two hypotheses for Mod Tools to work on this: one for design (whether to pursue a new design or use the current RC and Watchlist design) and one for engineering work

That sounds reasonable; I've created two placeholders under 1.3, one for engineering and one for design. We discuss as a team and decide if that's how we want to structure it and wordsmith hypotheses.

removing references to category changes from recentchanges / watchlist pages

Let's create a follow-up task for this and add it to the required hypothesis work.

agree

an open question is how would this impact watchlist?

Let's create a spike for this. I'm not sure this would be a Mod Tools spike, a DB spike or an effort from both teams.

Agree; I think this would be an "us" spike.

Regarding the rows coming from wikibase, I looked at arwiki that has around 760K rows from wikidata (2.3 times the actual number of edits in the wiki). I found a couple of issues:

• O aspect and aliases. Somehow many pages are subscribing to the O aspect and as result, they are getting all alias changes for that item even in languages that has nothing to do with them. That alone is responsible for ~20% of wikidata injected rows.
• Statement modifier collapse is a problem. C.P1, ..., collapse to C in general after twenty statements (I think 20, not sure). This is causing a lot of random edits on statements being injected on rc We probably should bump the threshold a bit.
• Changes to qualifiers and references. They are also triggering a lot of injections while they don't change anything (and I don't think client wikis actually load the references, at least it's quite rare AFAIK). That alone is also responsible for another 20% of rows being injected.

I ping Lydia and some other folks in WMDE so it could get investigated and improved.

In T396489#10951832, @Scardenasmolinar wrote:

Thank you, @jsn.sherman and @Ladsgroup, for all of your work on this ticket! I have a few comments on some follow-up work/tickets.

adding the new special page

I would create two hypotheses for Mod Tools to work on this: one for design (whether to pursue a new design or use the current RC and Watchlist design) and one for engineering work

removing references to category changes from recentchanges / watchlist pages

Let's create a follow-up task for this and add it to the required hypothesis work.

an open question is how would this impact watchlist?

Let's create a spike for this. I'm not sure this would be a Mod Tools spike, a DB spike or an effort from both teams.

Categorization changes (CatWatch) design has always been somewhat weird. I can't imagine when I would want to see regular changes mixed with category changes. Usually just one of those. So I wouldn't expect many people to rely on them in the recent changes. I think people have them displayed mainly in the watchlist (you choose which categories are relevant for you). They can also be displayed in related changes (RecentChangesLinked), but there is "that one flaw" (T89582).

So perhaps the design choices made 10 years ago did not meet users' expectations, and a redesign would be appreciated.

On large wikis, if there is no condition on rev_timestamp, just add a condition to be the last 24 hours

I think this is a copy-paste of a typo. There is no condition on rev_timestamp whatsoever.

In T396489#10951308, @jsn.sherman wrote:

In T307328#10715066, @matej_suchanek wrote:

[...]

Redundancy of rc_new, rc_type and rc_source

MariaDB [wikidatawiki_p]> SELECT DISTINCT rc_new, rc_type, rc_source FROM recentchanges;
+--------+---------+---------------+
| rc_new | rc_type | rc_source     |
+--------+---------+---------------+
|      0 |       3 | mw.log        |
|      0 |       0 | mw.edit       |
|      1 |       1 | mw.new        |
|      0 |       6 | mw.categorize |
|      0 |       5 | wb            |
+--------+---------+---------------+

See also T36320#8440663, T244080 and T74157.
[...]

The recommendation is to keep rc_source and drop rc_new and rc_type. I see this as shared work between moderator tools and sre. We (moderator tools) would update lookups to replace rc_new & rc type to use rc_source. sre would modify the database afterwards.

Note that rc_type is a normalized integer, whereas rc_source is a string. rc_type is potentially more efficient.

In T396489#10952849, @matej_suchanek wrote:

On large wikis, if there is no condition on rev_timestamp, just add a condition to be the last 24 hours

I think this is a copy-paste of a typo. There is no condition on rev_timestamp whatsoever.

Yup, it's on rc_timestamp. The idea is when you go to recentchanges (with js enabled) you are redirected to default value which is currently 30 days (I see days=30) that is useless but if we set the default to days=1 for large wikis, while in reality it won't make a difference (you at most can see a couple of hours), it makes sure with complex filtering or other problems (optimizer bugs, etc.), mariadb would still only scan 1/30th of rows.

In T396489#10951308, @jsn.sherman wrote:

The recommendation is to keep rc_source and drop rc_new and rc_type. I see this as shared work between moderator tools and sre. We (moderator tools) would update lookups to replace rc_new & rc type to use rc_source. sre would modify the database afterwards.

Note that rc_type is a normalized integer, whereas rc_source is a string. rc_type is potentially more efficient.

I considered it but decided against it since then now you have to build a system to make sure extensions introducing new rc_types don't overlap which is a non-trivial task also the gain is small since the text is short (int takes 4 bytes, a two letter string takes three bytes, they are not that different) and last but not least rc table isn't that large and is the de-normalization table, it's broken in different ways. So it's not worth the overhead of normalizing (specially adding a new table for normalization)