I don't do rants...normally

Normally, I don't do rants. But now I feel compelled to debunk the - IMO - downright slanderous post entitled "14 reasons not to use MySQL or other mid-range database management systems" by Curt Monash - an otherwise seemingly un-offensive blogger.

Anyway - here's my maybe somewhat emotional analysis. But really, this post is a load of cr*p! I mean, more than half of the "reasons" are not even about MySQL (as in the database - not the company), and the other aren't even reasons.

Judge yourself.

Many enterprises get quantity discounts. License and in some case even maintenance fees may not be bad at all.

You make it seem like this is an advantage! With MySQL, *everybody* gets a volume discount. We don't care if you're big or small, if you need many MySQL servers you simply pay *one single flat fee* to cover for support for *all you can eat*

http://www.mysql.com/products/enterprise/unlimited.html

Who cares if the system contains code for features you don’t need? Hardware is really cheap these days.

This is not a reason to go for a high end database - merely something that might not hamper you in doing so. That said, it's not that hard to argue why less is more. Take for example MS SQL's xp_cmdshell which lets you run an arbitrary OS-level command. Well, if your app has an injection vulnerability, an extra feature like this just allows a cracker more room to do damage. So it really pays off to be sure your database is exactly that - a database.

If you already have DBAs on staff, how much work is it to administer a few more small systems? Besides, an Oracle or SQL Server DBA has access to some pretty good remote tools, which let her administer many database servers at once.

I don't see how this is an argument to buy into high end databases. First of all, if you run MySQL or any database that is anything worth to you, you need a DBA. Period. The "besides" is hilarious - what's makes you think MySQL DBA's do not have such tools? In many shops I've seen Oracle and MS SQL DBAs use remote desktop or shell access to manage their databases, in much the same way as many MySQL DBAs manage theirs. And, in addition MySQL Enterprise comes with an enterprise monitor which lets you manage and monitor many, many (hundreds) of MySQL instances remotely.

http://www.mysql.com/products/enterprise/monitor.html

You can even get it for trial:

http://www.mysql.com/ent-trial-reg-2007/

If you run a Windows-only shop, why not go Microsoft soup-to-nuts?

heh, maybe because you want to rely on what you know will work - not on what you are promised that it will work. Sometime ;) Seriously - the choice for MS vs non-MS is all about choice vs piece of mind. Nobody ever got fired for buying Microsoft, so many IT managers buy a site-wide all MS license to get some piece of mind. They get some assurances that because it's MS everything is integrated and aligned and whatnot. But if you unpack the shiny boxes - that's when you find out how incredibly compatible MS is with MS. Not.

Contrast that to open source - not just MySQL. If it doesn't work or you don't like what you see: fine, grab a new. Try that with MS. Once people have bought into it, they feel compelled to make it work, why else did they spend all that money on it? We're talking about a company that can't even keep their frigging *browser* compatible between minor versions....seriously.

SQL Server used to be a mid-range DBMS, and still plays that role in many Oracle and DB2 shops today.

heh, this is hardly an argument. "Rain is wet, and still falls into many seas world-wide". I mean, how can this be a reason not to go for MySQL or another open source database?

Early on, Microsoft did a great job of usability engineering on SQL Server administration tools.

I won't contest that they did. But we are seeing it no being gradually replaced with more and more MS Visual Studio headaches. Is that what people really want?

Largely in response to Microsoft competition, Oracle radically improved its own tools. For sufficiently simple databases, installation and administration really aren’t that hard in any of the high-end DBMS.

gah...so how is this an argument for not choosing for MySQL? Seems like you should have titled your article "14 reasons why I like MS SQL" - not "14 reasons why people should not choose MySQL"

Oracle, SQL Server, DB2, and Informix all offer cheap or free low-end editions, with good upwards compatibility. Those might happen to meet your deployment needs, now and in the future.

I won't contest that the "express" editions are very useful development tools. But you must be joking when you are saying that these are serious deployment platforms. In fact, if you like these "Express" editions so much, you should be in favour of more MySQL, more postgres, more SQLite. Because those products are the sole reason that finally made the big vendors look for a way to make their overpriced offerings more appealing. Go rewind Tom Kytes Oracle XE announcement podcast - they started giving it away for free because they are basically forced to.

If your application grows so quickly that you really do wind up needing a high-end database management system underneath, you won’t have to rewrite it.

That's a load of it. MySQL is all about scaling beyond the scale-up threshold. Don't buy into bigger and more expensive - buy more and inexpensive.

http://www.mysql.com/why-mysql/scaleout.html

Most high-end database management systems have more robust datatype support than most mid-range DBMS, the PostgreSQL family of products excepted.

Not sure what this means exactly - I guess this is not specific enough for me to recognize anything that has to do with MySQL.

Upstart mid-range database management systems have a variety of maturity issues. What are the most common kinds of error messages you see in a typical week? If you use the Web a lot, MySQL errors may be in the top three. Those memory buffers seem to fill to the choking point all too often.

So now MySQL is an upstart? heh...yeah, you're right they've been around only 12 years. Personally the most common errors I see on the web are broken IE-specific pages but I guess that's another story. (actually I'm curious if that's in your top three as well) The most likely reason is that most dynamic websites are LAMP sites - MySQL is simply most ubiquitous. I don't know how ever you were going to substantiate your "memory buffers" remark but most mysql errors I see on the web are "out of connections" - i.e. the site grew so hard they need to phase in another MySQL instance.

Individual features may also not be very mature yet. MySQL has long offered stable transactions and decent clustering, but not necessarily with the same storage engines (and not necessarily either in the most common configurations). And how is performance on relatively new features like declarative referential integrity, user-defined functions, or stored procedures?

Referential integrity relatively new...boy you must be an old fart to call something that's been in there for more than 7 years "relatively new". And what about the new features in Oracle and MS SQL? I don't hear you complaining about the "relatively new" partitioning features in Oracle...

There are more and better third-party tools for popular high-end DBMS than there are for upstart mid-range database management systems.

And all of those are adding support for what database again? Right, MySQL, because at some point developers keen on those tools moved at some point to MyQL

Nobody you know ever got fired for recommending a traditional, over-engineered computing platform.

I think you hould make that your slogan Curt.


"On the whole, I think there should be a lot more use of mid-range database management systems than there is today. But the case isn’t entirely one-sided."

Well, you sure make a fine point out of making it look like a one sided issue.

Gartner on the Sun / MySQL deal

Heh, who knew we'd see the day that Gartner would write this:

Recommendations for all enterprises
...
MySQL is capable of supporting clusters, delivering atomic, consistent, isolated and durable (ACID) functionality and handling some mission-critical applications. Evaluate MySQL as an alternative to IBM DB2, Oracle and other DBMSs.

Well, fact of the matter is, they did write it in a recent report on the MySQL / Sun deal. You can download the Gartner analysis right here free of charge.

MySQL Cluster Study Guide getting good comments

One of the major projects I worked on last year was the MySQL 5.1 Cluster DBA certification. I did the background research to come up with a sensible scope and outline of the exam, wrote most of the questions and actively looked for feedback from our cluster engineering and development team as well as consultants that are deploying MySQL Cluster in real-world situation.

For example, I got great feedback on the exam from MySQL Cluster Senior Software Engineer Stewart Smith

...Response from the exam is overwhelmingly positive and as it’s new, afterwards I discussed a few questions with Roland (in fact, all the ones I got wrong). The (good!?) news is that I legitimately brain-farted on some.

... this isn’t some trivial know-a-tiny-bit-and-score-really-high certification...this is a serious one).

I may have heard at some point one of our people saying they thought it was the best certification exam we have...

I guess that could've turned out worse, heh? ;-)


I was also involved in the preparation and coordination of the creation of the accompanying study guide, did a fair share of reviewing the content and eventually I authored almost 3 chapters.

All in all it was a big project, and I learned a great deal in the process. Unfortunately, due to a number of reasons it took a while before we could actually publish the book, but now that we did, we are getting a number of pretty good comments. Take for example, Alan Snelson ordered the book at lulu.com en judging from his blog he enjoyed it a lot:

I booked the exam a couple of weeks ago thinking I might need some incentive to fully digest MySQL Cluster details. I need not have worried, the book provides a great introduction to MySQL Cluster in easy-to-read chapters bringing you up to speed in no time, much more entertaining than the reference manual!

Thanks Alan for your kind words! I'm glad you passed the exam.

Are you curious about the MySQL Cluster Certification? You can read more about it in the candidate guide, and if you feel lucky, you can even take a free evaluation test before you sign up for the real thing.

Enjoy, and good luck with your Exams ;-)

TeProFoKATeMUR - News from The Project Formerly Known as The MySQL UDF Repository

Hi all - boy, have I got news for you!

I would like to announce that The MySQL UDF Repository is no more. The rumour is that its growing popularity has caused it to be noticed by some folks at MySQL AB's legal department, kindly requesting that the project's name be changed to something even cooler.

However, these are just rumours - this is only the internet, so relying on a first-hand source, I can safely confide in you that the project's name and website are being changed mainly out of religious conviction to offer even more and better free UDFs for the MySQL database.

During the transition, the project can be referred to using this unpronounceable symbol:



However, in some circles, people refer to this symbol as TeProFoKATeMUR, which is believed to be an acronym of ThE Project Formerly Known As "ThE MySQL UDF Repository". The real incrowd however prefers to call it simply The Symbol.

The latest news that I heard through the grape vine is that the Project's identity crisis may already be over as I am writing this. I hear people whispering that a new name for the project will be announced shortly, and although the final name is not yet released it will most likely be either one of "The Order of Valiant Knights In Defense of the Honour of Fast-performing Freely Available Functions for MySQL" or simply The UDF Repository For MySQL.

I wouldn't know whether it is good or bad that the project is abandoning it's name. What I do know is that the project's new website is looking way better than the old one.



It has got an easier to remember URL too: http://www.mysqludf.org/. The Project's google group has not moved though - it is still available at http://groups.google.com/group/mysql-udf-repository and welcoming new members all the time.

If this is indicative for the other things the project has to offer, then these changes sure are promising. In terms of last developments, it is believed that the group is working on a UDF library for MySQL that wraps around libcurl to enable internet access using MySQL functions:

mysql> select * from mysql.func;
+----------------------------+-----+----------------------+----------+
| name                       | ret | dl                   | type     |
+----------------------------+-----+----------------------+----------+
| http_request               |   0 | lib_mysqludf_curl.so | function |
| lib_mysqludf_curl_info     |   0 | lib_mysqludf_curl.so | function |
| http_version_1_0           |   2 | lib_mysqludf_curl.so | function |
| http_version_1_1           |   2 | lib_mysqludf_curl.so | function |
| http_method_get            |   2 | lib_mysqludf_curl.so | function |
| http_method_delete         |   2 | lib_mysqludf_curl.so | function |
| http_version_none          |   2 | lib_mysqludf_curl.so | function |
| http_method_head           |   2 | lib_mysqludf_curl.so | function |
| http_method_post           |   2 | lib_mysqludf_curl.so | function |
| http_method_put            |   2 | lib_mysqludf_curl.so | function |
| http_method_trace          |   2 | lib_mysqludf_curl.so | function |
| http_authtype_basic        |   2 | lib_mysqludf_curl.so | function |
| http_authtype_digest       |   2 | lib_mysqludf_curl.so | function |
| http_authtype_gssnegotiate |   2 | lib_mysqludf_curl.so | function |
| http_authtype_ntlm         |   2 | lib_mysqludf_curl.so | function |
| http_authtype_any          |   2 | lib_mysqludf_curl.so | function |
| http_authtype_anysafe      |   2 | lib_mysqludf_curl.so | function |
| url_encode                 |   0 | lib_mysqludf_curl.so | function |
| url_query                  |   0 | lib_mysqludf_curl.so | function |
| url_encode_component       |   0 | lib_mysqludf_curl.so | function |
+----------------------------+-----+----------------------+----------+
20 rows in set (0.00 sec)

mysql> select lib_mysqludf_curl_info();
+-----------------------------------------------------------------------------------+
| lib_mysqludf_curl_info()                                                          |
+-----------------------------------------------------------------------------------+
| lib_mysqludf_curl version 0.0.1/libcurl/7.16.4 GnuTLS/1.6.3 zlib/1.2.3 libidn/1.0 |
+-----------------------------------------------------------------------------------+
1 row in set (0.11 sec)

mysql> set @page := http_request('http://www.mysqludf.org/' as url);
Query OK, 0 rows affected (0.04 sec)

mysql> select substring_index(substring_index(@page,'</title>',1),'<title>',-1);
+-------------------------------------------------------------------+
| substring_index(substring_index(@page,'</title>',1),'<title>',-1) |
+-------------------------------------------------------------------+
| UDF Repository for MySQL - Home                                   |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

An interesting year ahead for people interested of MySQL UDFs....

Speaking at the 2008 MySQL Conference

Yes - it's that time again ;-)


The new year has just started which means the MySQL Conference and Expo is little more than 3 months away. I am very glad to have the opportunity to attend, and even more glad to be speaking.

I will be doing two sessions:

• Writing MySQL UDFs - A 3 hour tutorial for people that want to get started writing MySQL UDFs. PHP and/or Java skills recommended - No pre-existing C skills required.
• Grand tour of the Information Schema and its applications - a session that demonstrate how to make practical use of the information_schema

I'll provide some more details about my sessions shortly, but enough of that for now. Instead, I'd like to share a list of some of the sessions (in no particular order) I am looking forward to attend:

• Astronomy, Petabytes and MySQL, Kian-Tat Lim; Stanford Linear Accelerator Center:

  The Large Synoptic Survey Telescope (LSST) will produce more than 100 petabytes of data when it goes on-line in 2014. All the non-pixel data will be managed by a database. This database is expected to contain some 50 billion astronomical objects, 3 trillion detections of those objects, and all the project’s metadata; a single copy will amount to tens of petabytes.



• Architecture of Maria, the New Transactional Storage Engine for MySQL - Michael "Monty" Widenius; MySQL AB: Overall design of this new transactional (MVCC) storage engine.


• DBSlayer: A Simpler Way To Proxy, Derek Gottfrid; The New York Times:

  The DBAccessLayer or DBSlayer (http://code.nytimes.com/projects/dbslayer) for short is a lightweight, open source, web aware proxying and pooling layer for MySQL used for backend architecture at nytimes.com. Assembled out of a few open-source libraries, DBSlayer communicates with clients using HTTP as a protocol and JSON as an encoding, instead of the custom binary protocols common to other proxying mechanisms. This enables the DBSlayer to easily interoperate with any programming languages that speak HTTP and JSON.



• External Language Stored Procedures for MySQL, Antony Curtis and Eric Herman; MySQL AB:

  With version 5.0, MySQL delivered a stored procedure implementation and here we extend it to provide general support for external language stored procedures. We detail the philosophy and design of the external language stored procedure framework and its implementation within the server and libraries. We then also detail how an external language plug-in is written using the simple skeleton plug-in which exercises the basic interfaces between the server and external stored procedures.



• MySQL Cluster with Replication for Financial Transactions - Cicero Torteli; Paggo
• Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community - Farhan Mashraqi; Fotolog:

  Fotolog is a top 19 Internet destination with more than 12 million members, 315 million photos and more than 3 billion page views a month. Through modifications to its data architecture, Fotolog was able to serve four times the number of users using the same number of database servers. A non-conventional, hybrid presentation that conveys the importance of scalability, performance tuning and schema optimizations in a practical way.

• Geographical Information and OpenGIS on MySQL - Markus Franz, BF Blogform Search GmbH:

  In this presentation, Markus Franz shows how to efficiently handle geographical information in a MySQL based environment. The presentation covers setup of a structured database, calculation of distances between two or more points, social tagging for data and solutions to automatically guess geo tags from existing content. This session is highly recommended for those who look for powerfull ways to enhance existing business infrastructures with geographical information.

• MySQL Proxy Wizardry - Giuseppe Maxia; MySQL AB:

  Are you tired of the limiting stored routines language? Can you distribute queries to several servers transparently? Try the power of MySQL Proxy. You will see an apparently normal MySQL server do all the above, and much more, with the addition of MySQL Proxy, a lightweight application that extends the server features on the fly.



• The Lost Art of the Self Join Beat Vontobel, Meteonews AG:

  Can you solve a Sudoku with only one SELECT statement? No temporary tables, SQL extensions, stored procedures or functions, not even non-relational features such as ORDER or GROUP BY—just plain joins and predicates? You can! With a combination of (admittedly quite a few) self joins.



• Developing INFORMATION_SCHEMA Plugins, Mark Leith; MySQL AB:

  A walk through of a very basic ‘HELLO WORLD’ INFORMATION_SCHEMA table, showing a user the very bare minimum that is required to create a pluggable I_S table. Then, examples of making some more interesting plugins – such as including the SIGAR OS statistics gathering library to collect CPU, Memory, Disk, Network and OS Configuration data within I_S tables, and a general ‘df -h’ INFORMATION_SCHEMA table.

• EXPLAIN demystified, Baron Schwartz; The Rimm-Kaufman Group, LLC:

  I learned EXPLAIN by studying source code, documentation, and learning from the query optimizer team. I verified everything I learned with complex test cases. Along the way I discovered how to reverse-engineer EXPLAIN into a query execution plan. Finally, I wrote a program to do this—MySQL Visual Explain, the first of its kind. In this session I’ll teach EXPLAIN from the ground up.

And this is just the tip of the iceberg!

There is just so much good stuff, it's hard to single out a few. Anyway - take a look at the schedule yourself. Word is that you can get Early Bird discounts up to somewhere in Februari, and there is also a 15% Alumni discount for attendees of past conferences. Check out the details here.

CU @ UC!!!

Calculating the Financial Median in MySQL

I believe I found a new method to calculate the median in MySQL. I would not be surprised if this method has been figured out by somebody else already. However, I can't seem to find any resources on the internet describing this method, so for now I flatter myself by assuming the method is original.

(Please do post your comments to this blog to correct me on that should I be wrong so I have a chance to rectify.)

The method I'm describing is a one-pass, pure SQL method. It does not require subqueries, cursors or user variables. However, it does rely on the MySQL specific functions GROUP_CONCAT() and SUBSTRING_INDEX()

I'll be maintaining a snippet for this method at MySQL Forge.
If you want to know what the median is, and how my snippet works, read on.

Some background

Like the mean and the mode, the median is an important metric to characterize the distribution of values in a collection. If we have a ordered collection of (numerical) values, the median is the value for which the number of entries that has a value that is higher than the median is exactly equal to the number of entries that has a value that is lower than the median. If there is an odd number of entries in the collection, the value of the median corresponds to the value of the entry that lies exactly in the middle of the list. If there is an even number of entries, the median is calculated as the mean of the two middle values.

MySQL offers a number of aggregate functions. Unfortunately, MySQL does not offer a function to calculate the median.

Even though MySQL does not support a MEDIAN() function natively, it is still possible to calculate it. You can:

• install one of the numerous UDF's floating around on the internet


• Use pure SQL like suggested in Chapter 23 of Joe Celko's SQL for Smarties

The snippet

Here's a snippet that shows how to calculate the median replacement cost for a film in the sakila sample database:

select
        (
            substring_index(                          -- left median: max value in lower half:
                substring_index(
                    group_concat(                     --   list all values in ascending order
                        f.replacement_cost
                        order by f.replacement_cost
                    )
                ,   ','
                ,   ceiling(count(*)/2)               --   left half of the list 
                )
            ,   ','
            ,   -1                                    --   keep only the last value in list
            )
        +   substring_index(                          -- right median: min value in upper half:
                substring_index(
                    group_concat(                     --   list all values in ascending order
                        f.replacement_cost
                        order by f.replacement_cost
                    )
                ,   ','
                ,   -ceiling(count(*)/2)              --   right half of the list 
                )
            ,   ','
            ,   1                                     --   keep only the first value in list
            )
        ) / 2                                         -- average of left and right medians
        as median
from    sakila.film f;

(For the latest version, refer to MySQL Forge)

So, how does this snippet work? In the remainder of this post, I'll explain the inner workings of this method in a top-down fashion.

The mean of the left and right median

The method I'm describing always takes the mean of the 'left' and 'right' median.

select
        (
            left-median(f.replacement_cost)
        +   right-median(f.replacement_cost)
        ) / 2                                         -- average of left and right medians
        as median
from    sakila.film f;

(Note that the usage of left-median() and right-median() is just an explanation of the structure - in reality there are not two distinct functions by that name)

The terms 'left median' and 'right median' are not common so they need an explanation.

Let's visualize the process to determine the median. We can do this by imagining that we have an ordered list of values and that we point our left index finger to the lowest value in the list and our right index finger to the highest value in the list.

Now, we look at our fingers. If there is more than one entry between our right and left finger, we move or left finger one entry to the right and our right finger one entry to the left, and we keep doing that until there are no more entries between our left and right finger. Once we're there, the value of the entry that is pointed to by our left finger is the 'left median' and the value of the entry pointed to by our right finger is the 'right median'.

If we had an even number of entries, then the left and right median each correspond to distinct entries - if there was an odd number of entries then the left and right median correspond to one and the same entry.

At any rate, once we found the left and right median, it is clear that their mean is the true median. If we have an even number of entries, we have to calculate the mean of the two middle values anyway, and if there is an odd number of entries, taking the mean of two identical values results of course in that same value which does thus result in a correct value for the median.

GROUP_CONCAT: an ordered list of values

In the example, we use GROUP_CONCAT to generate a list of values in ascending order:

GROUP_CONCAT(                     --   list all values in ascending order
    f.replacement_cost
    ORDER BY f.replacement_cost
)

This gets us a string consisting of concatenated replacement_cost values in ascending order, separated by the default separator, which is a comma (',').

We use the same list in both the calculation of the left and the right median.

Note that the length of the concatenation result returned by GROUP_CONCAT() is limited. By default, it is as small as 1024 bytes. Personally I think this is way too small so I have it configured to be 64K by default. You can set the length at runtime too like this:

    SET group_concat_max_len := 65535

You can specify larger values than 65535 too, and I suspect that the maximum packet size is the practical maximum:

    SET group_concat_max_len := @@max_allowed_packet

To inspect the current value, you can do this:

    SELECT @@group_concat_max_len

Getting the 'left' half of the list

Once we have the list of values, we can split it in two halves with little effort. We do this using the SUBSTRING_INDEX() function.

The SUBSTRING_INDEX() function processes a string argument and gets a substring based on the position of a particular occurrence of another substring.

In this case, the comma ',' is the substring that separates the values in our ordered list. But of all the commas in the list, which occurrence of the comma do we need to find?

Suppose our list contains 4 values. Then, these values are separated by three comma's:

values: '1,2,3,4'
commas:   ^ ^ ^
          1 2 3

If we want to divide this list in two equal halves, then the second comma is the divisor between the left and right halves of our list. With SUBSTRING_INDEX() this expression would get us the left half of the this list:

    SUBSTRING_INDEX('1,2,3,4', ',', 2) -- the substring up to the 2nd occurrence of ','

and the result will be:

    '1,2'

So now we have the first half of the list, and by definition, the last entry in that list, '2' is the left median of the original list '1,2,3,4'.

Now what if we would've had an odd number of entries in our list? Supose our list would've been like this:

values: '1,2,3'
commas:   ^ ^
          1 2

In this case too, we need the second comma to end up with a left substring that has the left median as last entry in the list (which also happens to be the proper median because this is a list with an odd number of entries).

As it turns out, we can conveniently generalize the SUBSTRING_INDEX expression like this:

    SUBSTRING_INDEX(list, separator, CEILING(#entries/2))

In other words, if we divide the number of entries in our list by two, and then round to the nearest higher integer, this gives us the particular occurrence of the separator what we are looking for to halve our list as required. Of course, calculating the number of entries is simply a matter of using the COUNT() aggregate function.

Excising the 'left' median

To actually obtain the left median itself, we just need to excise the last value from the left half of the list of values. We do this by applying SUBSTRING_INDEX() again.

Again, we need to make a substring in terms of the occurrence of the comma that separates the values in our list. This time, we need to get the substring found directly after the last comma in the list. With SUBSTRING_INDEX() we can conveniently express this in the following manner:

    SUBSTRING_INDEX(list, separator, -1)

This means: search the list from right to left and find the first occurrence of the separator. Return the substring that appears after the separator (that is, the substring appearing on the right hand of the separator).

Getting the 'right' median

The process to obtain the right median is a mirror of obtaining the left median: instead of obtaining the last value in the left half of the ordered list of values, we now need to obtain the first value of the right half of the list. This is actually as simple as reversing the sign of the occurrence argument in the SUBSTRING_INDEX() calls:

    left half:   SUBSTRING_INDEX(list, separator,  CEILING(COUNT(*)/2))
    right half:  SUBSTRING_INDEX(list, separator, -CEILING(COUNT(*)/2))

    last entry:  SUBSTRING_INDEX(list, separator, -1)
    first entry: SUBSTRING_INDEX(list, separator,  1) 

A few remarks

I think that in many cases, this can be a fair method to calculate the median. The advantage of this method is that it is relatively fast because the query itself is relatively simple.

It would be interesting to see how this method behaves when handling millions of rows. Maybe I will run some benchmarks on that later on.

In the mean while, feel free to post your thoughts, suggstions or critique on this blog.

Arnold Daniels' lib_mysqludf_xql featured on MySQL devzone

Wohoo! Jon Stephens just published an excellent article on the possibilities for working with XML and MySQL on the MySQL devzone!

I'm very proud that he chose to highlight the lib_mysqludf_xql library of MySQL User Defined functions authored by Arnold Daniels. Some time ago, Arnold joined the MySQL UDF Repository and so far he's been the most active member of the group.

Arnold also took the initiative to set up a new website for the MySQL UDF Repository, and we will soon move all content and libraries from the old location to the new site. (The main reason it hasn't happened yet is all due to me - i need to make time to move the old content to the new site).

If you want to discuss lib_mysqludf_xql or if you want to joint the UDF repository, sponsor our work or contribute in any other way, just go to our google group and post your questions.