Change 602699 had a related patch set uploaded (by Matthias Mullie; owner: Matthias Mullie):
[mediawiki/extensions/MachineVision@master] Allow prioritisation of images shown in "popular" tab

https://gerrit.wikimedia.org/r/602699

Change 602700 had a related patch set uploaded (by Matthias Mullie; owner: Matthias Mullie):
[mediawiki/extensions/MachineVision@master] Start using priority column when selecting images to review

https://gerrit.wikimedia.org/r/602700

Change 602701 had a related patch set uploaded (by Matthias Mullie; owner: Matthias Mullie):
[mediawiki/extensions/MachineVision@master] Drop unused index

https://gerrit.wikimedia.org/r/602701

These are the schema changes for DBA approval, once we've approved the series of patches internally (cc @Cparle) -- this is done

1. These queries would need to run on production (commonswiki) to set up the new schema:

ALTER TABLE /*_*/machine_vision_image ADD COLUMN mvi_priority tinyint(3) DEFAULT 0;
CREATE INDEX /*i*/mvi_priority ON /*_*/machine_vision_image (mvi_priority);

2. This one to update the existing data:

UPDATE machine_vision_image INNER JOIN machine_vision_label ON mvi_id = mvl_mvi_id INNER JOIN machine_vision_suggestion ON mvs_mvl_id = mvl_id SET mvi_priority = 127 WHERE mvs_timestamp < 20191201000000;

Note: can we do that one directly, or should we write a script to do it in batches?

This new query will be executed every time new images are requested: (to fetch a cutoff priority)

SELECT mvi_priority
FROM machine_vision_image
INNER JOIN machine_vision_label ON mvi_id = mvl_mvi_id
WHERE mvl_review = 0
GROUP BY mvi_priority HAVING COUNT( DISTINCT mvi_id ) >= 200
ORDER BY mvi_priority DESC
LIMIT 1

And then the existing subquery for actually fetching those images changes from this:

SELECT mvi_sha1
FROM machine_vision_image
INNER JOIN machine_vision_label ON mvi_id = mvl_mvi_id
INNER JOIN machine_vision_suggestion ON mvs_mvl_id = mvl_id
WHERE mvl_review = 0 AND mvs_timestamp < 20191201000000
ORDER BY mvi_rand DESC
LIMIT 10

to this:

SELECT mvi_sha1
FROM machine_vision_image
INNER JOIN machine_vision_label ON mvi_id = mvl_mvi_id
WHERE mvl_review = 0 AND mvi_priority >= $priority
ORDER BY mvi_rand DESC
LIMIT 10

Change 602701 abandoned by Matthias Mullie:
Drop unused index

Reason:
Combined mvi_priority_rand will not exist and we'll still use mvi_rand

https://gerrit.wikimedia.org/r/602701

The propose changes have been reviewed and agreed upon internally & the patch has been merged.
I've created a subticket for DBA to apply the schema changes.

Change 602699 merged by jenkins-bot:
[mediawiki/extensions/MachineVision@master] Allow prioritisation of images shown in "popular" tab

https://gerrit.wikimedia.org/r/602699

@matthiasmullie is this ready for QA? If so, can you move it to the QA column and tag Elena and let her know? Thanks!

This ticket requires multiple steps to be completed in sequence. The schema change has been merged & deployed to the cluster. The code to start using the new priority column has not yet been merged, so this is still in CR.
This is also not something that we'll be able to QA - it's only an internal change without any external/testable effects.

Oh! I thought I had merged that ... (goes and looks)

Change 602700 merged by jenkins-bot:
[mediawiki/extensions/MachineVision@master] Start using priority column when selecting images to review

https://gerrit.wikimedia.org/r/602700