Stripping Digits – The benefits of Benchmarking and Profiling

Carsten Pedersen (Certification Manager of at MySQL AB) read my entry about stripping digits, and has done a good write up here on how this function could be improved, a great deal.

Have a read of Carstens link, and my previous entry if you didn’t catch it, before we progress.

As you see, I mentioned that “it performed fairly well”, but I admit given the nature of the request (a quick IRC question) I didn’t run a benchmark on it compared to something like Carsten’s REPLACE solution. I actually went with the REGEXP as the original question was actually something along the lines of “If I have a string such as “joe123″ how I can I strip the digits from the end to return just “joe”. I wrote the quick function as an example of what you can do in 5.0, as the user was still 4.x anyway, really the answer would have been a little more along the lines of using REVERSE on the string and walking along the digits until you hit the first alpha (or [^0-9]), and then doing a substring up until that location.

However, Carsten was spot on when he picked up on this! This only goes to reinforce the fact that things should not be assumed on first glance, and should be benchmarked and tested rigorously. Carsten provides a great example of how to run a quick benchmark of function calls with the BENCHMARK function, which is the first logical step when testing any new stored functions that you may create. Thanks Carsten for the write up!

To take this one step further – I also read a great write up on “Benchmarking and Profiling” from “Pro MySQL” written by Mike Kruckenberg and Jay Pipes, which has generously been made available on the MySQL AB website here.

This gives some great information on using readily available open source benchmarking tools that allow would allow you to benchmark new functions within a number of different situations, with different data sets etc. It also gives some good information on profiling and diagnostics techniques, all of which is fantastic information.

This seems as good a time as any to introduce what I do for a living. I work for a company that sells and supports database management tools, primarily in the Oracle and SQLServer market place. If you read the section on profiling in the chapter above, a lot of it is exactly the kind of thing that we are putting together at the moment at Cool-Tools for the monitoring tool that we currently distribute – NORAD Surveillance. We’ve seen a huge upsurge of MySQL being picked up in the enterprise space, and we would quite frankly be foolish to sit back and ignore this. So we’re some way down the line of an early beta version of a plugin that Cool-Tools will provide that will monitor MySQL, as well as the underlying OS.

Here’s a quick sneak pic of the “Global Health Window“.. Although it’s a little boring on my little work desktop machine with no activity (that could really do with upgrading!), and still needs a few more variables to fill it out – it’s good enough to give an idea of what we are trying to do.. I ran a couple of the sys-bench scripts (test-insert and test-select) from a remote machine (the 192.168.3.50 host) to give it at least a little bit to look at..

I’ll post a full overview as and when – if there’s anybody out there that would like to try a beta copy of it as well, when it’s ready, feel free to fire me an email – mark at cool hyphen tools dot co dot uk – and I’ll see about getting it out to a few of you. We won’t consider people sending from email addresses such as hotmail ;)

Stripping digits

We had a question in #mysql on freenode yesterday, asking if there was a function to strip digits from a string. The answer is of course – not natively.

I’ve been playing around with Functions and Stored Procedures a bit lately though, trying to familiarise myself with the MySQL implementation fully, and wrote this quick function which does the job, although only in 5.0:

DELIMITER //

DROP FUNCTION strip_digits //

CREATE FUNCTION strip_digits (str VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE sub_start INT DEFAULT 0;
DECLARE res VARCHAR(50) DEFAULT '';

WHILE sub_start < LENGTH(str)+1 DO

IF SUBSTRING(str,sub_start,1) REGEXP '[[:alpha:]]' THEN
SET res = CONCAT(res,SUBSTRING(str,sub_start,1));
END IF;

SET sub_start = sub_start + 1;

END WHILE;
RETURN res;
END;
//

Here’s a couple of examples of it in action:

mysql> select strip_digits('foop123')//
+-------------------------+
| strip_digits('foop123') |
+-------------------------+
| foop |
+-------------------------+
1 row in set (0.00 sec)

mysql> select strip_digits('foop123a')//
+--------------------------+
| strip_digits('foop123a') |
+--------------------------+
| foopa |
+--------------------------+
1 row in set (0.00 sec)

mysql> select strip_digits('foop123ahgh63263jsdj')//
+--------------------------------------+
| strip_digits('foop123ahgh63263jsdj') |
+--------------------------------------+
| foopahghjsdj |
+--------------------------------------+
1 row in set (0.00 sec)

It even performs fairly well!

More to come in the “MySQL 5.0″ collection of blogs on Functions and Stored Procedures when I get time to sit down and do it justice! Even though I’ll never win a mug any more.. Hohum..