## 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
## Copy option
With denormalized namespace/title
```lang=sql
CREATE TABLE `page_tag` (
-- Link to page_tagwatchlist_def.ptd_id
pt_tag INT UNSIGNED NOT NULL,` (
-- Link to page_idwld_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
pt_page INT DEFAULT 0 NOT NULL,
-- Denormalized page_namespace-- A static enum, with 0 for private watchlist, 1 for shared list
pt_namespace wld_type TINYINT DEFAULT 0UNSIGNED NOT NULL,
-- Denormalized page_title-- A link to user_id, or zero for a public/shared list
pt_title VARBINARY(255) DEFAULT ''wld_owner INT UNSIGNED NOT NULL,
-- For ChangesListSpecialPage filtering etc.-- The list/label name, or an empty string for the main watchlist union
PRIMARY KEY (ptwld_namespace, pt_title, pt_tag_id) VARBINARY(255) NOT NULL,
-- For Special:EditWatchlist sortingPRIMARY KEY (wld_id),
UNIQUE INDEX (pt_tag_idKEY (wld_type, pt_namespacewld_owner, pt_titlewld_name)
);
CREATE TABLE `page_tag_def`watchlist_member` (
ptd_id INT UNSIGNED AUTO_INCREMENT NOT NULL,-- Link to watchlist_def.wld_id
-- A static enum, with 0 for private watchlist, 1 for shared listwlm_list INT UNSIGNED NOT NULL,
ptd_type TINYINT UNSIGNED DEFAULT 0 NOT NULL,-- Denormalized page_namespace
-- A link to user_id, or zero for a public/shared listwlm_namespace INT NOT NULL,
-- Denormalized page_title
ptd_owner INT UNSIGNED DEFAULT 0wlm_title VARBINARY(255) NOT NULL,
-- The list/label name, or an empty string for the main watchlist union-- For ChangesListSpecialPage filtering etc.
ptd_name VARBINARY(255) DEFAULT '' NOT NULLPRIMARY KEY (wlm_namespace, wlm_title, wlm_list),
PRIMARY KEY (ptd_id),-- For Special:EditWatchlist sorting
UNIQUE KEY (ptd_typeINDEX (wlm_list, ptd_ownerwlm_namespace, ptd_namewlm_title)
);
```
TODO: wl_notificationtimestamp
### Sample queries
Watchlist default filter
```lang=sql
SET @ptwld_id = (SELECT ptwld_id FROM page_tagwatchlist_def WHERE ptwld_type=0 AND ptwld_owner=@user AND ptwld_name='');
SELECT ...
FROM recentchanges
JOIN page_tagwatchlist_label_member ON rc_namespace=pt_namespace AND rc_title=pt_title
WHERE pt_id=@pt_id;
```
After fixing the rc_cur_id index to include rc_timestamp so that it can efficiently handle timestamp ranges:
```lang=sql
SELECT ... FROM recentchanges JOIN page_tag ON rc_cur_id=pt_page WHERE pt_id=@wlm_list=@wld_id;
```
Display subquery:
```lang=sql
SELECT GROUP_CONCAT(ptwld_name SEPARATOR '|')
FROM page_tag watchlist_member
JOIN page_tagwatchlist_def ON ptwld_id=pt_tagwlm_list AND ptwld_type=0 AND ptwld_owner=@user
WHERE pt_pagwlm_namespace=rc_cur_idnamespace AND wlm_title=rc_title
```
Filter by several watchlist labels, analogous to change_tag filter:
```lang=sql
SELECT DISTINCT ...
FROM recentchanges
JOIN page_tagwatchlist_member ON rc_cur_id=pt_pagewlm_namespace=rc_namespace AND wlm_title=rc_title
WHERE pt_id INwlm_list IN (...)
GROUP BY rc_timestamp, rc_id;
```