Sun will be bought by Oracle. Will this be the beginning of the end of MySQL?

MySQL has a serious market share. For that oracle has already tried to buy MySQL back in 2006. In an interview responding to the offer in 2006, MySQL chief Marten Mickos told that the reason for declining was that they wanted to keep MySQL an independent product. From that I assume that oracle wanted to merge Oracle DB and MySQL technology. Even though MySQL will be part of a major merger for which the rules aren’t yet clear, you might think that Oracle hasn’t changed their ideas about what they want with MySQL in the last 3 years.

Won’t MySQL just lose most of its market share if it become something else. Other …

Data stored in a database is often also useful for statistical purposes. If you own a web-shop you want to be able to create a report about turnover. You can get statistical information by using GROUP BY, eg.

SELECT DATE_FORMAT(invoice.date, '%M') AS `month`, COUNT(*) AS `invoice_count`, SUM(`invoice`.`amount`) AS `turnover`
FROM `invoice`
WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY MONTH(`invoice`.`date`)

month     invoice_count  turnover
January   84             9532.26
February  141            20857.61
March     91             10922.71
April     112            15044.48
May       101            9676.60 
June      137            12860.88
July      281            34291.20
August    191            26377.66
September 103            16324.78
October   99             12873.23

If you are selling a wide variety of products, you might like to see the turnover for each product category. You could do this with a simple GROUP BY …

I was reading this month’s php|architect. It has a nice article about EAV modeling. I had seen this db structure in other project, but didn’t know that it was called EAV. For those who don’t read php|architect, EAV describes a method of saving a large set of attributes, only some of which apply to an individual entity. Normally you would create a table, with a row for each entity and save each attribute in a column. With EAV you save each attribute as a row.

This makes selecting the data quite tedious. If you can life with some constraints, there is an easier way to do this.

The common way

How php|architect describes it (and how it’s normally described) is to split out the values over different tables for the different data types. The DB structure proposed in php|architect look like:

CREATE TABLE field_names (
    fid INT UNSIGNED PRIMARY KEY …

Send in by Ruben Daniels

Lustre is one of the most popular upcoming open source cluster file systems out there. When you want to run Xen’s from a SAN using Lustre you need to support both in the Linux kernel. Both XEN and Lustre are near mature products. This means there is support for it. But it’s quite difficult to find the right source and to combine it with the right kernel source of each. It took me a week of trial and erroring until I found a combination that worked. Since Google wasn’t much help I wrote this article so it might help you. This installation is Ubuntu Gutsy specific. You can start out with a basic Gutsy installation. Hardy is getting Lustre support, but at the time of this writing the package doesnt match the default kernel of Hardy.

First we apt-get some utilities and Xen packages

apt-get install quilt libc6-xen libxen3.1 …

Today I’m very pleased. My UDF lib ‘lib_mysqludf_xql’ has been mentioned in an article by Jon Stephens about ‘Using XML in MySQL 5.1 and 6.0′.

The lib_mysqludf_xql you can query relational data and return XML. It will give you the same functionality as SQL/XML in Oracle and MS SQL. Compares to other methods like using CONCAT or generating XML in a PHP script, this method is much easier and will perform much better.

You can find the library at mysqludf.org, including some documentation. You can also find a bunch of other nice libs there.

Do note that you need to have root privileges on the server to install MySQL UDFs.

The TelePort project is no longer under development.

This is a re-post of an article I wrote in back in August. We’re getting ready to release a new release of Javeline PlatForm at the end of this month. This release will be XForms compliant and has a lot of other new features as well. Before I start writing about that, I would like to put some attention on the communication layer aka Javeline TelePort.

—
PHPBuilder posted an article about sending a form using AJAX. This article shows how to post a simple form. But looking closely at the example, you can also see what the problem is using the plain XMLHttpRequest object and writing an implementation yourself.

First of all you to write some rather difficult javascript code. Next you need to completely …

I just got a question from Dirk Bonenkamp. He has a query which returns a number of contacts, but he wants to get no more than 5 contacts of per customer. After thinking really hard, I came up with a solution using variables and a subquery.

To test this idea, I used the ‘world’ database available at MySQL dev zone. This query selects no more than 5 countries per continent.

SET @a=0; SET @b="";
SELECT Name, Continent FROM (SELECT @a:=IF(@b=Continent, @a+1, 0) AS a, Name, @b:=Continent AS Continent FROM Country ORDER BY Continent) AS t WHERE a < 5;

This isn't one of the nicest looking solutions, but it works. Do you have a better solution? Please let me know :).

This week I have the honer of write Log Buffer. If you’re a fan of everything new and shiny, just sit back, because you’ll enjoy this weeks edition.

For those who’ve never heard of Arnold Daniels. I’m a software engineer at Javeline, an active member of the MySQL community and the author of lib_mysqludf_xql which is part of the MySQL UDF Repository. Well thats more than enough about me. I’m not here to get a job or a date, but to bring you the latest news.

Dreaming of Oracle features
Oracle has recently brought out version 11g of their database server. Just when you thought other DBMSs were catching …

It’s very nice that the LOAD XML patch finally got some attention. I wasn’t expecting it anymore. I worked on this with Erik a year ago. My C programming skill were to rusty at that time, that only my BNF code contribution was used and a lot of issues were never solved. With my C prog skills back up to a decent level, I;m planning to pick up the project. Now seems to be a better time than ever.

The current solution works well in certain situations, but definitely not in all. To insert referential data, you need to specify the id value at forehand, look at project_id in the example. That …

I often read articles saying to combine statements and send less queries. But you seldom see advise about splitting queries to improve performance. Here is what I came across just the other day:

SELECT IF(`uid_to`=@user, `uid_from`, `uid_to`) AS `uid`, `message`, `date`, `uid_to`=@user AS received
  FROM `beepme_msg`
  WHERE (`uid_from`=@user AND `uid_to`!=@user) OR (`uid_to`=@user AND `uid_from`!=@user)
  ORDER BY `uid_to`, `date`;

The query gets all messages send and received by the user, filtering our message the user send to himself. This looks like a good query since you’ll get all you data in one call. In reality it will mess up your performance, since it can’t use any indexes and will therefor use a table scan. And as we know, tables scans are slow.

The ‘OR’ statement basically messes this up, since only one index can be used per table. We can see what happens a bit better if …