* The ALTER TABLEs to run: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/MachineVision/+/602699/4/sql/patch-machine_vision_image-mvi_priority.sql
* Where to run those changes: **testcommonswiki & commonswiki**
* When to run those changes: **any time**
* If the schema change is backwards compatible: **yes**
* If the schema change has been tested already on some of the test/beta wikis: **it's currently on beta commonswiki**
* If it involves new columns or tables, if the data should be made available on the labs replicas: **new column (machine_vision_image.mvi_priority) can be made available**
---
Additional question (this is not a blocked for applying the schema change - this can be done later, but also great if it can be performed together with above schema change)
We'll want to update the existing data before we start to use this new column.
```
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;
```
Can we execute this large update directly, or should we write a script to do it in batches?
---
Additional review information WRT schema change:
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
```
Schema change progress:
[] commonswiki
** [] eqiad
** [] codfw
[] testcommonswiki (can be altered directly on the master - almost empty table)