From what I understand (This is bit beyond my expertise), for things like watchlist where a large number of rows are potentially scanned, its important to keep the tables narrow, so that more of it fits into a page of memory. (Assuming that's true... Which i have no idea. This is beyond my db knowledge) perhaps it makes sense to instead of adding more fields to the watchlist table, instead have a watchlist_info table in a 1:1 relationship which can have more information field (also move wl_notificationtimestamp over there).

So we could have watchlist table with only wl_id, wl_user, wl_namespace, wl_title

And then we could have watchlist_info with wl_id, wli_notificationtimestampe, wli_addedtimestamp, whatever else. (maybe denormalizing wl_user into it for ApiQueryWatchlistRaw?). If we implement expiring watchlists, expiry time could go in this table too.

Which would then hopefully keep the common case of no filtering fast, and requiring a join for more complex filters.

The downside is it would be harder to make an effective index. Although i suppose if we denormalize wl_user into watchlist_info we could index on (wli_user, wli_addedtimestamp) and join into watchlist.

Just a thought. DBA's should correct me if this comment is complete non-sense.

Great input @Bawolff. That sounds great to me, and is alternative solution. I do see something with this approach. In the approach mentioned in the proposal, when a watchlist entry is removed (unwatched), everything goes away with just 1 query (pretty fast I guess). But introducing another table would bring 1 more query as watchlist and watchlist_info would have to be updated upon watchlist entry removal. This is to make sure we don't have entries in watchlist_info that has been removed from watchlist.

So per above, I'm wondering if we should go for 2 queries or 1 query depending on how they're implemented (I guess). But I'm pretty sure that the overall efficiency will be better if we got for the db normalization approach you mentioned :)

Seems like this should be in the Inbox for TechCom rather than the Backlog.

Thank you very much @kchapman, and almost yes :) I was still supposed to develop the use-case section so the ticket can come into the Inbox in a full package. But if you think it's still fine to be there, no problem. I can still continue to beef it up while it's in "Inbox" :}

@D3r1ck01 oops, misunderstanding on my side. Please move to the Inbox when you are ready.

@D3r1ck01 oops, misunderstanding on my side. Please move to the Inbox when you are ready.

A billion thanks :) and yes, I'll move it to Inbox when it's ready to go :) But I think I understand why you moved it to inbox, because discussions already started on the ticket. @Bawolff suggested I put some more use-cases, so it was already complete (to me) until Brian's suggestion, now it's incomplete :) But thanks!

I should emphasize, I really don't know if what I said makes any sense, and a DBA should probably weigh in before doing much based on my comment.

Does this has something to do with T125991: Add wl_timestamp to the watchlist table? Is that some sort of duplicate?

In T209773#4765225, @Marostegui wrote:

Does this has something to do with T125991: Add wl_timestamp to the watchlist table? Is that some sort of duplicate?

Yes, it looks like this and that are duplicates. I'll leave it to someone else to figure out which one to close as a duplicate of the other and what, if anything, to copy to the one remaining open.

Looking at the watchlist table, it includes a wl_id which at least on mysql is declared as an int(10) unsigned NOT NULL AUTO_INCREMENT.

So if all you need is to find the order of insertion of elements in the watchlists, the id itself should give you a coarse-grained version of that, and you won't need a schema change for that - or am I missing something?

In T209773#4765225, @Marostegui wrote:

Does this has something to do with T125991: Add wl_timestamp to the watchlist table? Is that some sort of duplicate?

I just talked to @Addshore about this. He reminded me that the reason we considered recording when an item was added to the watchlist was to propose an alternative to watchlist expiry: the timestamp would allow people to query for "old" entries on their watchlist, and just select and remove them all. Whether that is a viable solution would have to be determined when doing UX design for the watchlist expiry request.

@D3r1ck01 is there community support for this idea? Do many people find this useful? It seems pointless to discuss the technical merit of your proposal if we don't know whether it is actually needed, or if anyone commits to implementing it. For now, this seems like a technical solution for a problem of which we do not know if we actually have it. It seems to me, this should be proposed as a feature. The Community Wishlist Survey would have been perfect for this, but the submission period for this ended a couple of weeks ago https://meta.wikimedia.org/wiki/Community_Wishlist_Survey_2019. There is however a relate proposal there that has a lot of support: https://meta.wikimedia.org/wiki/Community_Wishlist_Survey_2019/Watchlists/Watchlist_item_expiration.

@D3r1ck01 is there community support for this idea? Do many people find this useful?

Great question @daniel. I think we should find out about this!

The Community Wishlist Survey would have been perfect for this, but the submission period for this ended a couple of weeks ago https://meta.wikimedia.org/wiki/Community_Wishlist_Survey_2019.

Yes I agree this would have been a good place to pitch this but it's rather unfortunate that submissions are over but could we look for a means (if possible) to send this to community and get feedback? I don't know if a channel we can use but maybe others have ideas?

There is however a relate proposal there that has a lot of support: https://meta.wikimedia.org/wiki/Community_Wishlist_Survey_2019/Watchlists/Watchlist_item_expiration.

True!

To further clarify on the Blocked external/Not db team by Manuel, this seems a fairly simple and strightforward [famous last words], and not worrying storage-wise, you don't need any previous discussion with us to work on it- if agreed. We would like, however, to review potential new queries on implementation, to make sure indexing is used appropriately (it most likely will need a new index to filter on it, and that may not be that simple except on trivial usages- e.g. T209773#4783873 will need thorough review to support large watchlists). We are obviously always open for questions (ping us)- but we are not leading this work.

@D3r1ck01 wrote

I don't know if a channel we can use but maybe others have ideas?

There is no single channel to the community, since there is no single community. You could start with the English language Wikipedia, perhaps on https://en.wikipedia.org/wiki/Wikipedia:Village_pump_(proposals) or on the mailing list https://en.wikipedia.org/wiki/Wikipedia:Mailing_lists#Public_mailing_lists.

I'm moving this to the RFC backlog for now, since without commitment to resourcing, discussing the technical details seems pointless.

It appears this is a task for TechCom

This RFC proposes a new feature for the Watchlist component of MediaWiki. It involves a new database field, but also new internal interfaces, backend APIs, and a user interface. Such products features cannot be approved by TechCom. Before this can proceed, the next step is for the Growth team or another product manager to give this a go-ahead in terms of long-term roadmap. They might also want to talk with a designer to give you some pointers about how this should or shoudn't fit in.

After that, implementation could be volunteer-led of course, and we can start scheduling the schema change for review then as well.

Removing the DBA tag and subscribing myself instead. Once there are specific actions for DBA please re-add us and/or @mention me.

In T209773#4783479, @Joe wrote:

Looking at the watchlist table, it includes a wl_id which at least on mysql is declared as an int(10) unsigned NOT NULL AUTO_INCREMENT.

So if all you need is to find the order of insertion of elements in the watchlists, the id itself should give you a coarse-grained version of that, and you won't need a schema change for that - or am I missing something?

Sorting by wl_id would seem to fix the problem.

I'm thinking about recreating the watchlist table for T406843, so I'm looking for prior schema change requests. But I think this one can be declined on the basis that it's cheaper to use wl_id.