Weevil Genius

The EXPLAIN command in MySQL is an essential diagnostic tool for any database developer. With it you can learn the details of how the server processes any query and therefore where to spend your time optimizing the query and/or the server indexes. It should be the first thing you check when facing a slow query.

MySQL Proxy is a handy little utility for MySQL 5.0 and above which can be used to troubleshoot the database parts of an existing application without modifying the code to add lots of debugging statements. It speaks the MySQL binary protocol and can be placed between an application and its database, allowing you to inspect, log, and even manipulate the proxied queries and results.

A union is a type of join which allows you to glue two or more separate SELECT queries into a single result set. The structure of each query needs to be the same, meaning that the number of columns and their data types should match for all queries used in the union, and the column names or aliases from the first query determine the names of the final result columns.

Sooner or later when dealing with data that contains addresses, you’ll need to perform distance calculations. Some examples might be “which store is nearest to location X” or “display all hotels within 20 miles of the customer’s location sorted by distance”. This post deals with such calculations based on positions designated by latitude and longitude. The process of determining the latitude and longitude of an address is called geocoding and is outside the scope of this article.

A join in SQL is an attempt to match up rows in one table with rows in another. This allows you to treat the joined rows as if they were in one big table. The differences between the various join types mostly have to do with whether and how non-matching rows are discarded. Everything discussed below is generic SQL and not specific to MySQL unless noted. I use “record” and “row” here interchangeably.

Have you ever wondered which version of MySQL supports a particular feature? I certainly have. Here is a table listing various major features and storage engines and which versions of MySQL support them, from 3.23 through 5.1. I have cross linked to the MySQL online manual wherever possible, though there are still some gaps.

This came up recently at work, so I thought it would be good to lay down the basic permission system for MySQL accounts. These apply (except where noted) to MySQL 3 and higher.