Since I use MySQL for the statistical analysis on a project, I wanted to optimize the database queries and learned a lot about stuff like number theory, set theory and partial sums. I took my MySQL UDF, I've published two years ago, for this purpose and added new functions for a deeper statistical analysis. The project is around for a while, so it's time to share things with the public to start a discussion of how things could be further optimized. The source and a small documentation can be found on Github:

Read More »

Use DNS directly from your database

mysql> SELECT lookup('localhost');
+---------------------+
| lookup('localhost') |
+---------------------+
| 127.0.0.1           |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT reverse_lookup('127.0.0.1');
+-----------------------------+
| reverse_lookup('127.0.0.1') |
+-----------------------------+
| localhost                   |
+-----------------------------+
1 row in set (0.00 sec)

This is how you install these functions.

1. Build udf_example.so which is in your mysql source. (make udf_example.so)
2. Copy the udf_example.so file from your build directory to your plugin_dir.
3. Create the lookup and reverse_lookup functions

mysql> CREATE …

I’m pleased to announce the release of Memcached Functions for MySQL, version 1.1. It’s been a while since the 1.0 release, and I’ll use writing a book and having a baby as an excuse, but nevertheless, this release has several changes and new features:

• Fixed all the calls to libmemcached that were broken when libmemcached changed. This release works with Libmemcached 0.44
• Added my own string functions as the libmemcached string functions I relied on before were made local/private from the shared library
• Added memc_get_cas() and memc_get_cas_by_key() functions. You can now obtain the CAS value of an item! For instance:

mysql> select memc_servers_set('localhost:11211');
+-------------------------------------+
| memc_servers_set('localhost:11211') |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (0.00 sec) …

Note: It was my first attempt using the MySQL UDF Api back then. The library is still maintained and got some new features. Check out the new MySQL Infusion UDF.

Read the rest »

MySQL 5.1 Plugin Development

MySQL 5.1 Plugin Development, by Sergei Golubchik and Andrew Hutchings, Packt 2010. About 250 pages. (Here’s a link to the publisher’s site.)

This book is well worth reading for anyone interested in MySQL internals. I learned a lot from it. It is well-written and understandable. I cannot say that I’m planning to write storage engines or more advanced plugins, but I have a great many ideas how to improve MySQL, and I now understand more clearly which of those are suitable to write as plugins, and of what type of plugin is appropriate. I also think I have a better idea how much work these various ideas might involve.

The book begins …

Some background is needed. Crosstabs (also called data cubes or pivot tables, have been one of my favorite hacks for long time. In 2001 I wrote an article about a simple …

This Thursday (November 19th, 14:00 UTC), Patrick Galbraith will present memcached Functions for MySQL (UDFs). This session is about a suite of functions available to use with MySQL that allow you to store, retrieve and delete data, as well as most of the functions and operations that are available with libmemcached, such as server connectivity to the client, server status, client behaviors, and more. You can combine the fetching of data from one or more tables with the fetching of data from memcached and be able to apply any SQL operations on that result set such as LIMIT, sorting and other conditional operations.

For MySQL University sessions, point your browser to this …

This Thursday (November 19th, 14:00 UTC), Patrick Galbraith will present memcached Functions for MySQL (UDFs). This session is about a suite of functions available to use with MySQL that allow you to store, retrieve and delete data, as well as most of the functions and operations that are available with libmemcached, such as server connectivity to the client, server status, client behaviors, and more. You can combine the fetching of data from one or more tables with the fetching of data from memcached and be able to apply any SQL operations on that result set such as LIMIT, sorting and other conditional operations.

For MySQL University sessions, point your browser to this …

This Thursday (November 19th, 14:00 UTC), Patrick Galbraith will present memcached Functions for MySQL (UDFs). This session is about a suite of functions available to use with MySQL that allow you to store, retrieve and delete data, as well as most of the functions and operations that are available with libmemcached, such as server connectivity to the client, server status, client behaviors, and more. You can combine the fetching of data from one or more tables with the fetching of data from memcached and be able to apply any SQL operations on that result set such as LIMIT, sorting and other conditional operations.

For MySQL University sessions, point your browser to …