MySQL’s “explain <query>” does exactly what you described and better. It shows indexes used, exact number of rows scanned etc. It’s the first thing I do when I suspect a query taking time. It’s a pity that not too many backend engineers know about it now a days.
That only explains the particular query plan chosen for the query at that time. This might vary depending on the table statistics, query parameter or moon phase if you're unlucky. It is a very important tool, but doesn't solve the problem here that in the end the database is in control and decides how many rows to read, not you.
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).
Let's just say that MySQL docs are not super helpful when you want to quickly refer to something.
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).
