OFFSET. We looked at a rather "simple" case where we were paging on a primary or unique key, but in some cases, that's not good enough. This is especially true in a system where the primary or unique ... read more
LIMIT your expectations
OFFSETs next week, we're going to take a quick peek inside MySQL to understand how a simple SELECT ... LIMIT works, and while we're going to consider a simple case, we're also going to fold in some information we've already covered. ... read more
Extra confusing
EXPLAINing your queries more, I commend you: you're on the path to becoming a database query optimiser extraordinaire. You may have also identified that the Extra column isn't a singleton value. You may have even seen something weird like Using where; ... read more
filesort
ORDER BY clauses that are index-optimized away, and fun quirks aside, things have been pretty great. But what about when we can't optimize away the ORDER BY? ... read more
ORDER BY leveraging index optimisation, which essentially amounted to no sorting work whatsoever. But what would happen if we changed the query just a tiny bit? We'd get a gruesomely fun fact. ... read more
FirstMatch semi-join optimisation, and while it's pretty simple and fairly straight-forward, some queries may just not benefit from it. ... read more
PRIMARY index using a monotonic primary key. Before moving on to secondary indices, we'll consider random-order inserts. ... read more