Fun MySQL fact of the day: how many rows?

While trying to make sense the best I could of the code paths responsible for implementing SELECT ... LIMIT, I stumbled across a relic of MySQL. I'll admit, it seemed so strange at the time that I wasted too much time trying to understand what and why it existed before just checking the user manual (always check the manual). To my surprise, I discovered a "fun" feature that will be deprecated in MySQL 8.0.17 (and rightfully so!). Today, we're going to talk about SQL_CALC_FOUND_ROWS (or OPTION_FOUND_ROWS in the source code), and after today, we'll never talk about it again. Or use it. Ever (please).

On first glance, OPTION_FOUND_ROWS was weird. It is, of course, #defined as 1 << 5, which makes total sense: if a query's options mask has the 5th bit set, then, before returning the result set with a limit, keep reading all the rows, but then only return the limited result set afterwards. Makes sense to you, too, right? Sarcasm aside, how OPTIONS_FOUND_ROWS actually gets set in the query options mask was not apparent and somewhat of a fun fact in and of itself. Let me explain the best I can: to parse a query, MySQL uses YACC (Yet Another Compiler Compiler), which is a common library used for code generation for DSLs (Domain Specific Languages) like SQL. And, well, as it turns out, OPTION_FOUND_ROWS is set when SQL_CALC_FOUND_ROWS is passed in a query, such as SELECT SQL_CALC_FOUND_ROWS id FROM blah WHERE x = y LIMIT 10. Of course, that code doesn't exist until after you compile MySQL, which I hadn't done on my branch. That's the first fun fact of the day, and we'll probably circle back to this in the future.

Now, on to the second fun fact: what is SQL_CALC_FOUND_ROWS? While the MySQL manual can tell you exactly, when a query with the SQL_CALC_FOUND_ROWS option is executed, MySQL will continue to return the expected results, but it will also continue on past the LIMIT of a result set (as if the query didn't have a LIMIT) to determine how many rows the query would have returned. Then, when the query returns, you can retrieve this data using SELECT FOUND_ROWS(). Now, if we ignore the unexpected pain and suffering this option can cause in large tables, this feature can be pretty useful in theory: in just 1 query, you can fetch your result set and figure out how many matching records there are, which you can then use to figure out other things like how many pages there are. Fun.

Of course, you can also just run a SELECT COUNT(\*) FROM blah WHERE x = y to the same effect. And if you do, it's immediately clear to a larger number of developers what, exactly, you're trying to accomplish. All without magic. All without me making me think I've completely lost my mind for sure this time. Unless that's your goal; and, in that case, feel free to write to Oracle telling them to keep this terrible feature.