[HN Gopher] How to Read MySQL EXPLAINs
       ___________________________________________________________________
        
       How to Read MySQL EXPLAINs
        
       Author : creativedg
       Score  : 81 points
       Date   : 2023-03-30 11:22 UTC (11 hours ago)
        
 (HTM) web link (planetscale.com)
 (TXT) w3m dump (planetscale.com)
        
       | waynesonfire wrote:
       | [flagged]
        
         | firloop wrote:
         | This feels like a low effort comment that you could likely post
         | on most engineering blog posts on the home page.
        
           | waynesonfire wrote:
           | like this article and your post?
           | 
           | But whatever, I guess I'm the only one that finds discovering
           | new chatgpt uses cases helpful.
        
       | SeanA208 wrote:
       | What's the best resource for the equivalent of this page but for
       | Postgres?
        
         | wswope wrote:
         | Markus Winand's "Use The Index, Luke" site is the classic
         | reference. It covers most of the major DBs, but also gets into
         | PG specifics.
         | 
         | https://use-the-index-luke.com/sql/explain-plan/postgresql/o...
         | 
         | Understanding query plans is a universal skill that mostly
         | transcends database particulars, so even this Planetscale
         | article will serve as a good primer too.
        
         | qsort wrote:
         | [flagged]
        
           | happytoexplain wrote:
           | Note that the official docs are _not_ always the best
           | resource for a given topic (in general - I don 't know about
           | Postgres specifically). "What is the best resource" is a
           | totally reasonable question, and general opinion _may_ be
           | that the answer is the official docs.
        
         | mkl95 wrote:
         | Freshly translated (no affiliation whatsoever)
         | https://postgrespro.com/community/books/internals
        
         | fabian2k wrote:
         | As a tool to read the output:
         | 
         | https://explain.depesz.com/
         | 
         | Not entirely necessary when you're more familiar with it, but
         | still useful. And when starting it's much easier to read.
         | 
         | https://www.depesz.com/tag/unexplainable/
         | 
         | This series of blog posts is one option. But my impression is
         | that most of this comes simply with experience. At first the
         | main thing you'd look for is simply whether it uses the indexes
         | you want it to use, or if it does full table scans.
         | 
         | I'd also recommend to simply read the Postgres manual, at least
         | the parts that are not entirely about the syntax of various
         | commands. There is a lot of information in there that will help
         | to understand how Postgres works.
        
       | pritambarhate wrote:
       | Has anyone used PlanetScale's SaaS offering? How has been your
       | experience?
        
         | ithrow wrote:
         | No PITR
        
       | firloop wrote:
       | I know the blog post has MySQL in the title, but one point I'll
       | add for those of us working with Postgres: writing it as `EXPLAIN
       | (ANALYZE, BUFFERS)` adds buffer hits to each line of the output.
       | I've found that valuable to reveal what parts of a query are
       | hitting the hardware the hardest and there's seemingly no reason
       | to not add buffers to the output each time.
        
       | denton-scratch wrote:
       | My first experience with Oracle (this was a long time ago, and I
       | suspect they didn't even call it EXPLAIN). Anyway, I found it
       | significantly better and clearer than the EXPLAIN output from
       | Postgres or MySQL.
       | 
       | There was a query being emitted by the anti-fraud software of a
       | certain well-known US bank, that was taking 45 minutes to run,
       | which didn't please the fraud team. This fraud team weren't
       | highly-paid elites; they were drudge workers, as much at risk of
       | burnout as anyone.
       | 
       | Anyway, I was tasked with fixing this (low-importance) problem. I
       | managed to figure out what the problem was with the query, with
       | the help of EXPLAIN. I reconstructed the query so it ran in less
       | than 10 seconds, and submitted the patch.
       | 
       | It took two months for the DBAs to let me add the requisite index
       | to production. They'd given me a copy of the production database,
       | eventually, so I could test my change, and make sure it didn't
       | break anything else; but they still held it back. DBAs were very
       | anally-retentive in those days.
       | 
       | I got no credit for that (except from myself :-). I'm happy to
       | pat myself on the back for reducing the query time from absurd to
       | reasonable, and by a factor of 1/270.
       | 
       | I've never used Oracle since, by the way; it's always looked to
       | me like a blind alley. But $FINCORP used to like Oracle, and the
       | Oracle DBAs were treated like ghodz.
        
       ___________________________________________________________________
       (page generated 2023-03-30 23:01 UTC)