In my previous post about geo-spatial search in MySQL I described (along with other things) how to use geo-distance functions. In this post I will describe the geo-spatial distance functions in more details.

If you need to calculate an exact distance between 2 points on Earth in MySQL (very common for geo-enabled applications) you have at least 3 choices.

• Use stored function and implement haversine formula
• Use UDF (user defined function) for haversine (see below)
• In MySQL 5.6 you can use st_distance …

At the MySQL User's conference 2008 I did a tutorial on MySQL User-defined functions. At the same conference, Brian Miezejewski and Mark Atwood did a talk on aggregate UDFs.

In relation their upcoming talk, Mark wrote a blog post reporting that MySQL Aggregate UDFs are three times slower than equivalent native functions:I just proved to myself via testing and benchmarking that user defined aggregate functions are about three times slower than the call interface to the built-in functions.Later on he posted an update, …

Liveblogging: Extending MySQL by Brian “Krow” Aker

Brian wins the award for “most frequent great quotes during a talk”.

Before MySQL 5.1 a UDF was the only way to extend MySQL.

All you need in a UDF is: init() execute() deinit()

my_bool id3_parse_init(UDF_INIT *initid UDF_ARGS *args, char *message)

UDF_ARGS tell you about incoming args
char *message is the output that might return
args->arg_count is the # of args

WARNING: use STRICT mode in MySQL, otherwise there are tons of silent failures.

“When you work on databases you start to put everything in databases. Tip, don’t put a DVD into a database, because really long BLOBs aren’t actually supported….”

In MySQL 5.1, you can now install plugins (example is memcache_servers plugin):

mysql> INSTALL PLUGIN memcache_servers SONAME …

The MySQL User's conference will be held in less than a month from now!!!

This year there is quite a good number of sessions on adding your own functions and procedures, such as:

• 
  
• Advanced Stored Procedures
  
• A Tour of External Language Stored Procedures for MySQL
  
• Code Generators for MySQL Plugins and User Defined Functions (UDFs)
  
• …