Ever wondered how fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function against a "roughly equivalent" subquery. The idea -- and there may be shortcomings that are poisoning the results here, your comments welcome -- is to see how fast the SQL procedure code is at doing basically the same thing the subquery code does natively (so to speak).

Before we go further, I want to make sure you know that the queries I'm writing here are deliberately mis-optimized to force a bad execution plan. You should never use IN() subqueries the way I do, at least not in MySQL 5.1 and earlier.

I loaded the World sample database and cooked up this query:

PLAIN TEXT SQL:

1. SELECT sql_no_cache sum(ci.Population) FROM City AS ci
2.   WHERE …

This is the 150th edition of Log Buffer, the weekly review of database blogs. Someone accidentally left Dave Edwards‘ cage unlocked, and he escaped, thus leaving me with the pleasurable duty of compiling the 150th weekly Log Buffer.

Many people other than Dave are finding release this week. Giuseppe Maxia explains some details of MySQL’s New Release Model. Andrew Morgan announces a New MySQL Cluster Maintenance Release. Aleksandr Kuzminsky of the MySQL Performance …

The Kickfire appliance is designed for business intelligence and analytical workloads, as opposed to OLTP (online transaction processing) environments.  Most of the focus in the MySQL area right now revolves around increasing performance for OLTP type workloads, which makes sense as this is the traditional workload that MySQL has been used for.  In contrast,  Kickfire focuses squarely on analytic environments, delivering high performance execution of analytical and reporting queries .

A MySQL server with fast processors, fast disks (or ssd) and lot of memory will answer many OLTP queries easily.  Kickfire will outperform such a server for typical analytical queries such as aggregation over a large number of rows.

A typical OLTP query might ask “What is the shipping address for this invoice?”.  Contrast this with a typical analytical query, which asks “How much of this item did we sell in all of …

There seems to be an optimizer problem in 5.1, if you put a NULL in the IN clause of a SELECT. For example, given the following table:

CREATE TABLE foo (
    a INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (a)
);

Compare these two EXPLAINs:

mysql> EXPLAIN * FROM foo WHERE a IN (160000, 160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.06 sec)

mysql> EXPLAIN SELECT * FROM foo WHERE a IN (NULL, 160000, 160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 327680
        Extra: Using where
1 …

If you were interviewing to work at Percona, and I asked you "what does Using filesort mean in EXPLAIN," what would you say?

I have asked this question in a bunch of interviews so far, with smart people, and not one person has gotten it right. So I consider it to be a bad interview question, and I'm going to put the answer here. If anyone gets it wrong from now on, I know they don't read this blog!

The usual answer is something like "rows are being placed into a temporary table which is too big to fit in memory, so it gets sorted on disk." Unfortunately, this is not the same thing. First of all, this is Using temporary. Secondly, temporary tables may go to disk if they are too big, but EXPLAIN doesn't show that. (If I interview you, I might ask you what "too big" means, or I might ask you the other reason temporary tables go to disk!)

The truth is, filesort is badly named. Anytime a sort can't …

How smart is the MySQL optimizer? If it sees an expression repeated many times, does it realize they're all the same and not calculate the result for each of them?

I had a specific case where I needed to find out for sure, so I made a little benchmark. The query looks something like this:

PLAIN TEXT SQL:

1. SELECT sql_no_cache
2.    pow(sum(rental_id), 1),
3.    pow(sum(rental_id), 2),
4.    pow(sum(rental_id), 3),
5.    pow(sum(rental_id), 4),
6.    pow(sum(rental_id), 5),
7.    pow(sum(rental_id), 6),
8.    pow(sum(rental_id), 7),
9.    pow(sum(rental_id), 8),
10.    pow(sum(rental_id), 10),
11.   …

We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work.  The first thing these have in common is that they're generally some kind of loggable event.

The next characteristic of a lot of these systems (real or planned) is the desire for "real-time" analysis.  Our customers often want their systems to provide the freshest data to their own clients, with no delays.

Finally, the analysis is usually multi-dimensional.  The typical user wants to be able to generate summaries and reports in many different ways on demand, often to support the functionality of the application as well as to provide reports to their clients.  Clicks by day, by customer, top ads by clicks, top ads by click-through ratio, and so on for dozens of different types of slicing and dicing.

And as a result, one of the most common …

Yesterday (December 4th), Sergey Petrunia gave a presentation on what's new in MySQL Optimizer. Unfortunately, the slides didn't show up in the Dimdim presentation area, which also means that the recorded session only has Sergey's voice but not the slides. (I've filed a bug in Dimdim's issue tracker.) However, Sergey kept referring to slide numbers in his talk, so it should be fairly easy (just not as convenient as usual) to follow his recorded presentation.

The slides, together with links to the recording and the chat transcript, can be found on the MySQL University session page.

Next week …

This Thursday (December 4th), Sergey Petrunia will highlight what's new in MySQL Optimizer, with a focus on MySQL 5.1. Since Sergey is one of the main developers in charge of MySQL Optimizer, you can expect to get some in-depth insights that only very few people could provide.

This MySQL University session will start at 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow). The virtual meeting room will open 15 minutes before the hour. Point your browser to this address.

Here's the MySQL University schedule for the rest of this year. Only two more sessions to go in 2008:

This is a fun question I've been wanting to test for some time.  How much overhead does a trivial WHERE clause add to a MySQL query?  To find out, I set my InnoDB buffer pool to 256MB and created a table that's large enough to test, but small enough to fit wholly in memory:

PLAIN TEXT SQL:

1. CREATE TABLE `t` (
2. `a` date NOT NULL
3. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
4.  
5. INSERT INTO t(a) VALUES(current_date);
6. INSERT INTO t SELECT * FROM t;

I repeated the last statement until it got slow enough that I thought I could do a reasonable test; at this point there were 8388608 rows. I then optimized the table and restarted MySQL. The table ended up at 237MB.

Now let's see how long a table scan with no WHERE clause …