Does MySQL (or PlanetScale) provide a way to input a query and get a lower/upper bound on the number of rows the query will "inspect" when run?
This is a good point. You can control for some variables (like query parameters), via testing, however as table statistics change, so does query planning. For instance, your table gets bigger (or smaller) and MySQL chooses a different query plan.
There's almost no way to completely predict pricing in all but the simplest of apps. Overall, the pricing model seems untenable.
I'm reading MySQL docs [1] and I can't figure out which value in the example output corresponds to the "exact number of rows scanned".
> It’s a pity that not too many backend engineers know about it now a days.
I use EXPLAIN with PostgreSQL when needed but mostly focus on the execution time, not the number of rows scanned (reading a large row could be orders of magnitude slower than reading a row in a JOIN table of two integers in a row based storage engine).
Here's[1] an explain output that I ran just now. The row labeled "rows" is the actual number of rows scanned which as you can guess is bad because it's doing a full able scan.
I completely ignore the actual execution time and focus mostly on two things 1) indexes used; 2) number of rows scanned. If rows scanned is high it means I need to do something about it now even if the absolute query time is low. Either an index is missing or someone in the application code is querying the wrong way (random example, using a "like" on a full table).