I have a series of scripts that acts on a subset of entries in a large (2M rows) MySQL table. There are 10 instances in the application, and each instance works on id‘s ending in 0-9 from a single table. The ending digit could also be considered the modulus. Instance 0 works on id‘s ending in 0, instance 1 works on id‘s ending in 1, etc.
mysql> select id from table limit 10;
+---------+
| id |
+---------+
| 1099138 |
| 1811048 |
| 794297 |
| 985812 |
| 424120 |
| 353809 |
| 478882 |
| 506101 |
| 2184449 |
| 1189511 |
+---------+To see what progress the application is making or if it is stuck at I use a query grouping the id‘s remaining to be worked on by modulus:
mysql> select substr(id, -1) as modulus,count(*) from table where field='criteria' group by modulus with rollup;
+---------+----------+
| modulus | count(*) |
+---------+----------+
| 0 | 9 |
| 1 | 14 |
| 2 | 13 |
| 3 | 1985 |
| 4 | 10 |
| 5 | 16 |
| 6 | 16 |
| 7 | 11 |
| 8 | 10 |
| 9 | 20 |
| NULL | 2104 |
+---------+----------+The substr(id,-1) takes the id and returns the last digit (-1) as modulus. Then it’s grouped by modulus, and using rollup is summed at the end. As you can see the application still has 2104 id‘s to work on, and something is different about instance 3. Either there is an uneven distribution of id‘s ending in 3 (probably not the case, as id is primary key auto increment), or instance 3 in the application is borked.