1 I knew, but didn’t think of 2. Still find it amazing that they haven’t fixed 3 after all these years.

And now that I see how you actually find a minimum and maximum value such that the chunk size is 1000, I understand your remark about sparse too.

This sounds silly, but only when the numbers are sequential. If the numbers are sparse, then the number before 4000 may well be 2975, for all we know.

Good question.
There are a couple reasons:

1. BETWEEN is inclusive on both edges (a < = x AND x <= b); except for the very first chunk, I want it only half-inclusive (a < x AND x <= b). 2. More importantly, this is but the simple case of a more complex scenario where the key may be compound (see example in https://shlomi-noach.github.io/blog/mysql/how-common_schema-splits-tables-internals )

In such case you get something like this:


WHERE ((((`film_actor`.`actor_id` > '110')) OR ((`film_actor`.`actor_id` = '110') AND (`film_actor`.`film_id` > '513'))) AND (((`film_actor`.`actor_id` < '146')) OR ((`film_actor`.`actor_id` = '146') AND (`film_actor`.`film_id` < '278')) OR ((`film_actor`.`actor_id` = '146') AND (`film_actor`.`film_id` = '278'))));

(3). It is worth mentioning that MySQL does not do a good job at doing a range comparison using a compound key:
https://shlomi-noach.github.io/blog/mysql/mysql-not-being-able-to-utilize-a-compound-index
Which is the reason I need to break the condition into so many clauses.

Why not City.ID BETWEEN 3000 AND 3999 ? (and then start the next chunk at 4000)

Honest question