## Cross-join option
```lang=sql
CREATE TABLE watchlist_label (
-- unique id for row
wll_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
-- user_id for label creator
wll_user INT UNSIGNED NOT NULL,
-- label text
wll_name VARBINARY(255) NOT NULL,
PRIMARY KEY(wll_id),
INDEX wll_user_name (wll_user, wll_name)
);
CREATE TABLE watchlist_label_member (
-- Link to wll_id
wlm_label INT UNSIGNED NOT NULL,
-- Link to wl_id
wlm_item INT UNSIGNED NOT NULL,
PRIMARY KEY(wlm_label, wlm_item),
INDEX (wlm_item)
);
### Sample queries
Display subquery
```lang=sql
SELECT GROUP_CONCAT(wll_name SEPARATOR '|')
FROM watchlist
JOIN watchlist_label_member ON wlm_item=wl_id
JOIN watchlist label ON wll_id=wlm_label
WHERE wl_user=@user AND wl_namespace=rc_namespace AND wl_title=rc_title
```
Filter by several watchlist labels
```lang=sql
SELECT DISTINCT ...
FROM recentchanges
JOIN watchlist ON wl_namespace=rc_namespace AND wl_title=rc_title AND wl_user=@user
JOIN watchlist_label_member ON wlm_item=wl_id AND wlm_label IN (...)
GROUP BY rc_timestamp, rc_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 WatchedItemStore::duplicateEntry(), CLSP filtering
UNIQUE INDEX (wlm_namespace, wlm_title, wlm_list),
-- For Special:EditWatchlist sorting, could also be used for CLSP filtering
INDEX (wlm_list, wlm_namespace, wlm_title),
-- For Special:EditWatchlist sorting by age
INDEX (wlm_list, wlm_id),
-- 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:
```lang=sql
SELECT DISTINCT ...
FROM recentchanges
JOIN watchlist_member ON wlm_namespace=rc_namespace AND wlm_title=rc_title AND wlm_list IN (...)
GROUP BY rc_timestamp, rc_id;
```