Back to Top

Sunday, August 19, 2007

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.


Post a Comment

You can use some HTML tags, such as <b>, <i>, <a>. Comments are moderated, so there will be a delay until the comment appears. However if you comment, I follow.