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.
- 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)
- Rollout plan
- Rollout will be incremental to wikis. For a tentative plan, see {T240094#5807341}
- Queries:
- Create:
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:
DELETE `watchlist`, `watchlist_expiry` FROM `watchlist_expiry` JOIN `watchlist` ON (`we_item`=`wl_id`) WHERE `we_expiry` < NOW();
- Purge expired items explain:
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)