Tuesday, February 28, 2006

Today I was analyzing the behavior of the query optimizer, and I stumbled into a most curious case.I created two functions returning the extremes of a date range, and I wanted to see how many times those functions were called when used in a WHERE clause So I added log tracing instructions to both of them. The result was quite surprising.Let's set the environment first.

I can't imagine why this is happening. The only difference is that dt is now primary key. Instead of being called once, the routine is called twice. If I simply drop the primary key in t2, then the routine is called once per query, as expected.The result does not change if I use InnoDB tables instead of MyISAM.

Friday, February 17, 2006

Well, it came a bit late, but it's official (good thing I haven't bought a conference pass yet!)I'll be speaking at the fourth MySQL Users conference on Higher Order MySQL with this abstract:

Higher order functions are those that can accept a function as an argument or can produce a function as their output. MySQL recent addition of stored procedures and dynamic querying makes it possible to extend the DBMS features, especially for business intelligence and data warehousing. Functions that can create functions are useful to expand the language, producing complex SQL code quickly and accurately.

Some examples of what can be achieved with these kind of functions: global table CRC (as oposed to record CRC) for remote table comparison, cross tabulation (pivot tables in SQL), data on demand from compressed storage (indexed, as opposed to non-indexed data stored by the archive engine), enhanced metadata queries, where records from the information schema and records from data table create customized administrative tools. The applications are countless. The power of stored procedures, paired with dynamic queries can give developers the ability of doubling the features of MySQL DBMS. Higher order functions and procedures are one step beyond normal stored procedures. They give developers one templates to create standardized functions for similar tasks. While the basics of such discipline are better exploited in application languages (C, C++, Perl come to mind), dynamic SQL is perfectly capable of achieving important goals.

Apart from speaking, I will have a chance to meet in person several people that I know only by mail, and to meet again someone that I keep meeting (gladly) over and over at the conferences.Yes, I look forward to it!