[mariadb-query-optimization] "Pagination: Cursor-Based Instead of OFFSET" only covers ORDER BY id

[federico-razzoli]

I'm not sure this section is necessary, because the models I tested know this. But if you want to include it for weaker models, I think you should make the explanation more complete, because you omitted the non-obvious part.

This section only covers the case where the query has ORDER BY id. In practice, there are 3 more cases to cover:

1. ORDER BY [some columns], unique_not_null_column - As long as the last column is unique, the ORDER BY is ok. But the WHERE clause needs to filter by all columns.
2. ORDER BY [some columns], unique_not_null_column, more columns. Works, but ORDER BY can be simplified, as the columns listed after the first UNIQUE NOT NULL column don't affect the order. Also, WHERE must filter all the remaining columns.
3. ORDER BY non-unique columns. The LLM should append the primary key to the list of columns, and than modify the WHERE.

Proper WHERE and ORDER BY for pagination will look like this:

WHERE (brand, price, id) > ('nokia', 99.9, 24)
ORDER BY brand, price, id