4 ms·

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?

• CaveTech 5y ago
  Yes, through `EXPLAIN`.
• vishnugupta 5y ago
  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.
  • fabian2k 5y ago
    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.
    • unclebucknasty 5y ago
      >*might vary depending on the table statistics... 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.
  • rustc 5y ago
    > exact number of rows scanned etc 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). [1] https://dev.mysql.com/doc/refman/8.0/en/explain.html https://dev.mysql.com/doc/refman/8.0/en/explain.html
    • vishnugupta 5y ago
      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). [1] https://imgur.com/a/0Pj52QA https://imgur.com/a/0Pj52QA
  • redis_mlc 5y ago
    > exact number of rows scanned etc. Never heard the word "exact" used for number of rows from EXPLAIN. Source: MySQL DBA.