Although I’m a big PostgreSQL supporter, I started out as a MySQL user and still use MySQL daily, so I listen to the OurSQL podcast. In the latest episode (number 22) the topic was
Things To Avoid With MySQL Queries. While I picked up a few tips from it (and most of the things mentioned is applicable across the board, not just specifically to MySQL), I realized that pgAdmin, the GUI administration tool for PostgreSQL has a great feature (between the many) that it’s not talked about a lot: the visual representation for
EXPLAIN queries. Because what can you interpret easier, this:
Of course everything has two sides, so here is a small gotcha with pgAdmin: every time you access a database which doesn’t have the default encoding set to UTF-8, it will pop-up a warning saying that for maximum flexibility you should use the UTF-8 enconding. However what it fails to mention that if you don’t use the standard C or SQL_ASCII encoding, you will have to define the indexes with special operator classes if you wish for them to be useful for query execution.