## Cross-join option
**watchlist_label table**
| column | type | description
| ----- | ----- | -----
| wll_id | int | unique id for row
| wll_user | int | user_id for label creator
| wll_name | varchar 255 | label text
**watchlist_label_member table**
| column | type | description
| ----- | ----- | -----
| wlm_label | int | Link to wll_id
| wlm_item | int | Link to wl_id
## With denormalized namespace/title
```lang=sql
CREATE TABLE `watchlist_def` (
wld_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
-- A static enum, with 0 for private watchlist, 1 for shared list
wld_type TINYINT UNSIGNED NOT NULL,
-- A link to user_id, or zero for a public/shared list
wld_owner INT UNSIGNED NOT NULL,
-- The list/label name, or an empty string for the main watchlist union
wld_name VARBINARY(255) NOT NULL,
-- The number of members
wld_count INT NOT NULL,
PRIMARY KEY (wld_id),
UNIQUE KEY (wld_type, wld_owner, wld_name)
);
CREATE TABLE `watchlist_member` (
wlm_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
-- Link to watchlist_def.wld_id
wlm_list INT UNSIGNED NOT NULL,
-- Denormalized page_namespace
wlm_namespace INT NOT NULL,
-- Denormalized page_title
wlm_title VARBINARY(255) NOT NULL,
-- Earliest unseen revision timestamp
wlm_notification_timestamp BINARY(14) DEFAULT NULL,
-- For emulated joins, e.g. ActivityUpdateJob
PRIMARY KEY (wlm_id),
-- For Special:EditWatchlist sortingWatchedItemStore::duplicateEntry(), page move REPLACECLSP filtering
UNIQUE INDEX (wlm_listnamespace, wlm_namespacetitle, wlm_titlelist),
-- For Special:EditWatchlist sorting by age T209773, could also be used for CLSP filtering
INDEX (wlm_list, wlm_idnamespace, wlm_title),
-- For ChangesListSpecialPage filterSpecial:EditWatchlist sorting etc.by age
INDEX (wlm_namespace, wlm_titlelist, wlm_listid),
-- ApiQueryWatchlistRaw changed filter
INDEX (wlm_list, wlm_notification_timestamp)
);
```
### Sample queries
Watchlist default filter
```lang=sql
SET @wld_id = (SELECT wld_id FROM watchlist_def WHERE wld_type=0 AND wld_owner=@user AND wld_name='');
SELECT ...
FROM recentchanges
JOIN watchlist_label_member ON rc_namespace=pt_namespace AND rc_title=pt_title
WHERE wlm_list=@wld_id;
```
Display subquery:
```lang=sql
SELECT GROUP_CONCAT(wld_name SEPARATOR '|')
FROM watchlist_member
JOIN watchlist_def ON wld_id=wlm_list AND wld_type=0 AND wld_owner=@user
WHERE wlm_namespace=rc_namespace AND wlm_title=rc_title
```
Filter by several watchlist labels, analogous to change_tag filter:
```lang=sql
SELECT DISTINCT ...
FROM recentchanges
JOIN watchlist_member ON wlm_namespace=rc_namespace AND wlm_title=rc_title
WHERE wlm_list IN (...)
GROUP BY rc_timestamp, rc_id;
```