Context: For T378035: [EPIC] Collaborative contributions MVP, we want to implement a feature where event participants can indicate that an edit was made as part of an event. To do that, after someone makes an edit, we will check whether they are participating in an event that is currently ongoing and targeting the current wiki (information available via the other CampaignEvents table). If so, we will show a dialog to let them associate the edit and the event; see pictures in task description of T400953. Then, in the event details page, we will show a list of all edits associated with that event, both individually and in aggregated form; see wireframes in T402211.
Table definition
CREATE TABLE ce_event_contributions ( cec_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, cec_event_id BIGINT UNSIGNED NOT NULL, cec_user_id BIGINT UNSIGNED NOT NULL, cec_wiki VARCHAR(64) NOT NULL, cec_page_id INT UNSIGNED NOT NULL, cec_page_prefixedtext VARBINARY(512) NOT NULL, cec_revision_id BIGINT UNSIGNED NOT NULL, cec_edit_flags INT NOT NULL, cec_bytes_delta INT NOT NULL, cec_links_delta SMALLINT NOT NULL, cec_timestamp BINARY(14) NOT NULL, cec_deleted TINYINT(1) NOT NULL, INDEX cec_wiki_page_id (cec_wiki, cec_page_id), INDEX cec_event_user (cec_event_id, cec_user_id), PRIMARY KEY(cec_id) );
Table information
From wikitech
Should this table be replicated to wiki replicas (does it not contain private data)?
Data is partly public, partly private. More specifically, public replicas should only include rows such that: joining the ce_participants table on equal event+user yields a row where cep_private is false, and cep_unregistered_at is null. But, because this is in x1 and x1 tables are not publicly replicated, maybe we don't need any of that.
Will you be doing cross-joins with the wiki metadata?
Not with core tables, because we're in a different DB (cluster) anyway. However, some of the data that we store references core tables; for example, we have a column that references revision.rev_id. These are not foreign keys at the DB level though, also because they reference data from multiple wikis.
Size of the table (number of rows expected)
Well, initially 0. Then it'll grow as described below.
Expected growth per year (number of rows)
We aren't sure because there's currently no accurate tracking of this data anywhere (the P&E Dashboard would have higher estimates). As a starting point, we could probably assume an average of 1000 edits per event; given our target of 1800 events per year, that'd give 1.8M new rows per year as a generous estimate.
Expected amount of queries, both writes and reads (per minute, per hour...per day, any of those are ok)
- Writes: One write per associated edit, and no other writes. So, using the estimate above, 1.8M writes/year or about 5k writes/day.
- Reads: Only when someone goes to the metrics page. I don't think we have estimates for this, but if I were to guess, I'd put it in the order of magnitude of 10^4 per year.
Examples of queries that will be using the table.
SELECT SUM( IF (cec_bytes_delta > 0, cec_bytes_delta, 0 ) ) as positive_bytes, SUM( IF (cec_bytes_delta < 0, cec_bytes_delta, 0 ) ) as negative_bytes, SUM( IF (cec_links_delta > 0, cec_links_delta, 0 ) ) as positive_links, SUM( IF (cec_links_delta < 0, cec_links_delta, 0 ) ) as negative_links, COUNT( DISTINCT cec_user_id ) as participants, COUNT( DISTINCT cec_wiki ) as wikis, COUNT( DISTINCT CONCAT( cec_wiki, '|', cec_page_prefixedtext ) ) as pages, SUM( IF (cec_edit_flags & 1, 1, 0 ) ) as creations FROM ce_event_contributions JOIN ce_participants ON ( cep_event_id=cec_event_id AND cep_user_id=cec_user_id AND cep_unregistered_at IS NULL ) WHERE cec_event_id = 123 AND cec_deleted = 0
SELECT * FROM ce_event_contributions JOIN ce_participants ON ( cep_event_id=cec_event_id AND cep_user_id=cec_user_id AND cep_unregistered_at IS NULL ) WHERE cec_event_id = 123 AND cec_deleted = 0 ORDER BY cec_timestamp ASC, cec_id ASC LIMIT 50
SELECT * FROM ce_event_contributions JOIN ce_participants ON ( cep_event_id=cec_event_id AND cep_user_id=cec_user_id AND cep_unregistered_at IS NULL AND (cep_user_id = 333 OR cep_private = 0) ) WHERE cec_event_id = 123 AND cec_deleted = 0 AND ( cec_bytes_delta < 42 OR (cec_bytes_delta = 42 AND ( cec_timestamp < '456' OR ( cec_timestamp = '456' AND cec_id < 789 ) )) ) ORDER BY cec_bytes_delta DESC, cec_timestamp DESC, cec_id DESC LIMIT 500
SELECT * FROM ce_event_contributions JOIN ce_participants ON ( cep_event_id=cec_event_id AND cep_user_id=cec_user_id AND cep_unregistered_at IS NULL ) WHERE cec_event_id = 123 AND cec_deleted = 0 AND ( cec_wiki > 'xywiki' OR ( cec_wiki = 'xywiki' AND ( cec_timestamp > '456' OR ( cec_timestamp = '456' AND cec_id > 789 ) ) ) ) ORDER BY cec_wiki ASC, cec_timestamp ASC, cec_id ASC LIMIT 100
UPDATE ce_event_contributions SET cec_page_prefixedtext = 'Foo' WHERE cec_wiki = 'awiki' AND cec_page_id = 1234
UPDATE ce_event_contributions SET cec_deleted = 1 WHERE cec_wiki = 'awiki' AND cec_page_id = 1234
The release plan for the feature (are there specific wikis you'd like to test first etc)
Features related to this table will be behind a feature flag. We'll test it in beta first, and then on to production wikis.
Open questions (obsolete)
What recommendations would DBAs have on indexes? As can be seen from above, all queries will filter on cec_event_id, then do a join on cec_event_id and cec_user_id, then allow pagination/sorting on the following unique tuples:
- cec_page_prefixedtext + cec_wiki + cec_timestamp + cec_id
- cec_wiki + cec_timestamp + cec_id
- cec_user_id + cec_timestamp + cec_id
- cec_timestamp + cec_id
- cec_bytes_delta + cec_timestamp + cec_id
The current implementation has an index on (cec_event_id, cec_user_id) to cover the base filtering, but this won't do for pagination on other fields. I'm not sure what would help though. I did some quick tests but it would still filesort, e.g. with an index on (cec_event_id, cec_user_id, cec_timestamp, cec_id) in the "Table default, organizer" example above.