With the standard data warehousing queries we have a fact table and dimension tables and we join them.
For example, the fact table (Table size: 5M rows, ~2G in size) from my previous Loose index scan vs. covered indexes in MySQL post:

    CREATE TABLE `ontime_2010` (
      `YearD` int(11) DEFAULT NULL,
      `MonthD` tinyint(4) DEFAULT NULL,
      `DayofMonth` tinyint(4) DEFAULT NULL,
      `DayOfWeek` tinyint(4) DEFAULT NULL,
      `Carrier` char(2) DEFAULT NULL,
      `Origin` char(5) DEFAULT NULL,
      `DepDelayMinutes` int(11) DEFAULT NULL,
      `AirlineID` int(11) DEFAULT NULL,
      `Cancelled` tinyint(4) DEFAULT NULL,
    ... more fields here ...
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

(this is not the best possible fact table as the data is not aggregated by I’ll use it for now).

And we have those …

Before launching into this, I must give due deference to Mogens Nørgaard’s landmark article, You Probably Don’t Need RAC (YPDNR), available here, but originally published Q3 2003 in IOUG Select Journal.  Mogens showed that you can be a friend of Oracle without always agreeing with everything they do.

At Blue Gecko, many of our remote DBA customers have been asking us about Golden Gate.  In July 2009, Oracle bought Golden Gate Software, just one of several companies that have developed log-based replication mechanisms for Oracle and other databases.  This was one of many major acquisitions by Oracle in 2009, including Sun and Relsys. But unlike most of Oracle’s acquisitions, …

Lets say we have a table storing mail messages and we need to show user’s mailbox: messages sent “from” and “to” the specified user.

Here is our table:

CREATE TABLE `internalmail` (
  `mail_id` int(10) NOT NULL AUTO_INCREMENT,
  `senderaddress_id` int(10) NOT NULL,
  `recipientaddress_id` int(10) NOT NULL,
  `mail_timestamp` timestamp NULL DEFAULT NULL,
... message body, etc ...
  PRIMARY KEY (`mail_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And our query:

select * from internalmail
 where (senderaddress_id = 247 or recipientaddress_id = 247 or recipientaddress_id = 0) 
and mail_timestamp > '2010-08-01 12:30:47' 
order by mail_timestamp desc 

In this query we show all messages from and to user_id = 247 plus all messages to system user (user_id=0). We need to show only messages for the last 3 months and show the most recent messages first.

To speed up the query we can try creating …

A very interesting presentation by Kaj Arnö about the State of the MySQL Community. The initial part of the presentation is about the history of how things happened with MySQL, Sun and Oracle, then it describes the current state of our community. The final part of the presentation is fairly enough promotion towards SkySQL for whom Kaj is EVP of Products. I really enjoyed it and would recommend it to all of you interested in past, present and future of “MySQL”.

check it out here

Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information on it. – Samuel Johnson

Sooner or later, a DBA will be asked for some information from their databases that will need to be well formatted and past the limitations of SQL. The requester will also want to run the report on an ad hoc basis.  And the user can not wait for a script or program to be written. Oh, can you set it up so they can add or drop items of data or change it around on the fly too?

There are a number of report writing programs out there and three with active open source communities are BIRT, Jaspersoft, and Pentaho, among others.  They all roughly do the same thing — take data from your instance and turn it into a good looking report.

Calpont has published three guides to help you get started with these three reporting programs and they …

A catchy headline, and I believe more accurate than Oracle Puts the Squeeze on SMBs with MySQL Price Hike (Network World) and MySQL price hikes reveal depth of Oracle’s wallet love [MySQL Jacking up MySQL Prices] (The Register). Slightly more realistic is Oracle kills low-priced MySQL support (again The Register).

First, let’s review what Oracle has actually done: they ditched the MySQL enterprise Basic and Silver offerings. For Oracle, that makes sense. Their intended client base is “enterprise” (high end, think big corporates) and their MySQL sales and cost structure reflects this. It’s not a new thing that came with MySQL at Oracle, because MySQL at Sun …

After this mornings twitteriffic panic where people thought Oracle had removed innodb from the mysql community version, its better to clear some things out.

As a friend explained, mysql and innodb are free as in free speech and free beer (the dream). Hence Oracle model version is only talking about ENTERPRISE versions of mysql NOT the popular COMMUNITY version. All of you who used to go to downloads.mysql.com to get your mysql + innodb fix still can do so for FREE.

Oracle have changed their products page, but haven’t deemed important to explain the changes to the MySQL community. Oversight or intentional, the news that innodb won’t come with MySQL for free anymore with the community version is NOT TRUE.

Because Sharing is Caring

So this morning I was welcomed to work with a “Have you heard the news?” … Good lord I just woke up for God sakes.

Just like the storm back in April 2008 when Sun bought MySQL, another (possibly the perfect storm) happened in April 2009 when Oracle bought Sun, today the latter storm took us be “surprise” to a certain extent when Oracle released their new Editions model showing innodb removed from the classic edition. People are shouting all kinds of things at Oracle and there is a lot of confusion but I think it’s better to wait and see! We are a strong community and we won’t let this storm take us down ….. we are stronger than the Andrea Gail!

If you check out twitter be careful what you believe and don’t …

Monty Program along with various guests and our friends at the recently founded SkySQL recently wrapped up a meeting in Istanbul Turkey. Sadly it rained most days but we had a good time and got a lot done.

Besides learning how to say “Şerefe” which is “Cheers” in Turkish, we discussed the future of MariaDB, our companies goals, the MariaDB knowledge base and many other topics. Since I am the web guy, I lead the presentation / talk on the KB and thought I would share some highpoints of it with you.

If you don’t know, the KB is located at http://kb.askmonty.org. The goal is to provide one location for MariaDB and MySQL documentation along with a place to ask questions (and have developers actually answer them). To start we have both information licensed under the GPL about …

Mea Culpa, Mea Culpa, Mea Maxima Culpa!
My fault, my fault, it’s all my fault! Why? Well because new versions = new bugs :) = wait till they are fixed, then use it!

As an ubuntu fan I just had to install 10.10 GA … and as a MySQL DBA I can’t run apt-get install mysql …. not even in my nightmares can I do that. Why? Well because if you are a car mechanic you don’t take your car for repairs to another mechanic and not ask what was done on it do you? You do it yourself right? That is what I did, I installed mysql 5.1.50, the same binaries I had downloaded and used on my 10.04 machine and loaded a dump. Woops, mysql crashed big time. I figured, lets install 5.1.51, load and … boom, again crashed.

The same dump, is loading fine on my 10.04 ubuntu server running both 5.1.50 and 5.1.51.


101018 12:48:26 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that …