The Community Tech team has started working on a new project called Watchlist Expiry. We need a new DB table to store the expiration timestamp and watchlist item ID.
* Predicted usage:
** We might see an increase in usage of the Watchlist due to this new feature.
** This new table will never be bigger than the existing `watchlist` table.
** Have a periodic purging of the `watchlist` and `watchlist_expiry` tables -- purging all items that have timestamp that is before the current time from both tables. https://phabricator.wikimedia.org/T235005#5714344
*** Purging will happen about twice a day to cover different timezones.
*** It will help purge the `watchlist` table.
** Common xxx expiration date
* Limitations
** Minimal expiration date allowed is 1 day. We will guarantee expiration within a day of the chosen date, without guaranteeing exact hour.
** To start, we will enable expiration within a preset of day/week/month, but later the product will allow choosing a custom date.
** We can set a maximum limit for expiration that makes sense (6 months or a year)
* Queries:
** Create:
```lang=sql
CREATE TABLE `watchlist_expiry` (
`we_item` int unsigned NOT NULL PRIMARY KEY,
`we_expiry` binary(14) NOT NULL
) /*$wgDBTableOptions*/;
CREATE INDEX `we_expiry` ON `watchlist_expiry` (`we_expiry`);
```
** Purge expired items:
```lang=sql
DELETE `watchlist`, `watchlist_expiry` FROM `watchlist_expiry` JOIN `watchlist` ON (`we_item`=`wl_id`) WHERE `we_expiry` < NOW();
```
** Purge expired items explain:
```lang=sql
EXPLAIN DELETE `watchlist`, `watchlist_expiry` FROM `watchlist_expiry` JOIN `watchlist` ON (`we_item`=`wl_id`) WHERE `we_expiry` < NOW();
+------+-------------+------------------+--------+-------------------+---------+---------+-------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------------+--------+-------------------+---------+---------+-------------------------------+------+-------------+
| 1 | SIMPLE | watchlist_expiry | ALL | PRIMARY,we_expiry | NULL | NULL | NULL | 1 | Using where |
| 1 | SIMPLE | watchlist | eq_ref | PRIMARY | PRIMARY | 4 | wiki.watchlist_expiry.we_item | 1 | |
+------+-------------+------------------+--------+-------------------+---------+---------+-------------------------------+------+-------------+
2 rows in set (0.00 sec)
```
*** PLEASE DO NOT TAG DBA TEAM TILL COMMUNITY TECH REVIEWS AND APPROVES THIS TICKET*********