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
SELECT has LIMITs
LIMIT. And, if you'll believe it, it was exactly 2 months ago today that we first (and last!) considered LIMIT, but there's lots more fun than you ... read more
filesort and we'd discussed two important variables: sort_buffer_size and max_length_for_sort_data. But there's 1 more important variable that you may even find yourself wanting to change on a query-by-query basis: max_sort_length. ... read more
filesort. Or, well, more specifically, about MySQL's handling of temporary files (of type IO_CACHE). ... read more
filesort uses files
filesort can use different sorting algorithms depending on variables, we didn't discuss the various modes that filesort has. The behaviour of these modes can even make the sorting algorithm filesort chooses seem pretty unimportant. ... 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
ORDER
ORDER BY clause are implementation specific. We also observed that in InnoDB, this implementation-specific behaviour tends to be the order of records as they are sorted in the index from which ... read more
Using where quite a lot lately and you might be wondering, "there must be a better way". There is. In MySQL 5.6 and beyond, queries may leverage the Index Condition Pushdown Optimisation. ... read more