Creating optimal queries for databases

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:

or 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.

, , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *