## 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 sorting by age, emulated joins
PRIMARY KEY (wlm_id),
-- For Special:EditWatchlist sorting, page move REPLACE
UNIQUE INDEX (wlm_list, wlm_namespace, wlm_title)
-- For ChangesListSpecialPage filtering etc.
INDEX (wlm_namespace, wlm_title, wlm_list),
-- 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;
```