MySQL became wildly successful in part because it had built-in, simple replication. Sure, it had lots of interesting failure scenarios and was not great at first — it is much better these days — but it was nevertheless successful because there was a single, out-of-the-box, not-very-complex way to do replication. I have opined many times before that this was one of the killer features missing from PostgreSQL. I think that can large explain why MySQL became more popular more quickly.

The new killer feature is automatic sharding, in my opinion. If you’re not accustomed to the word, “sharding” means partitioning of a large dataset across many servers.

It is easy to poke fun at MongoDB’s current limitations, but for all that, it has a story to tell about sharding. There is One Right Way To Do It in MongoDB, and it’s a part of the product.

I don’t see sharding …

In case you live under a rock, the MySQL conference starts on Tuesday. There are 8 concurrent tracks of content. Aside from my own talks, here are the sessions I would most like to see.

On Tuesday:

• Tutorial: Innodb and XtraDB Architecture and Performance Optimization. Peter’s talks are always great, and this is a perennial favorite. If you have never been, you should go to this one. You will realize how little you really know about InnoDB/XtraDB, and how much it matters.
• Tutorial: …

Somebody asked me for a useful example of Oracle 11gR2′s new analytical LISTAGG function that uses a WITHIN GROUP syntax. They’d noticed an update to the askTom that showed how to use it. This post shows how to list values without a displayed aggregation column and how to use a JOIN and GROUP BY clause with the new analytical feature.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

COLUMN list      FORMAT A10
COLUMN last_name FORMAT A10
COLUMN names     FORMAT A42
COLUMN members   FORMAT 9,990
 
 
SELECT   m.account_number AS account
,        c.last_name AS last_name
,        LISTAGG(c.first_name||DECODE(c.middle_name,NULL,NULL,' '||SUBSTR(c.middle_name,1,1)||'.'),', ')
           WITHIN GROUP (ORDER BY 2) AS names
,        COUNT(*) AS members
FROM     contact c INNER JOIN member m …

COUNT() function returns a number of rows returned by a query. In a popular opinion COUNT(*) should not be used on InnoDB tables, but this is only half true.

If a query performs filtering on any column, there is no relevant difference in how COUNT(*) will be executed regardless of the storage engine. In any such case MySQL has to look for matching rows and then count them.

In the following queries COUNT(*) can be used without any negative impact on performance:

SELECT COUNT(*) FROM mytable WHERE id = 12345
SELECT COUNT(*) FROM mytable WHERE is_enabled = 1
SELECT COUNT(*) FROM mytable WHERE username LIKE 'a%' AND is_enabled = 1

The real difference is when no filter is specified in WHERE clause, i.e. when query counts all rows in a table. MyISAM maintains cached row count for each table, so it can always return the value …

With the 3rd edition of High Performance MySQL finally complete, I’ve begun work on the 4th edition. As you know, technology moves much faster than printing presses, and a book is outdated very quickly, so this is a never-ending project. I’m also outlining the 5th edition in anticipation of starting it immediately afterwards.

I’m looking for your input on what I should cover in the new edition. Should I discuss MySQL’s intra-query parallelization across multiple CPU cores? Should I explore how cloud computing platforms enable higher performance at lower cost than dedicated hardware? Should I explain the bizarre bug in MySQL’s datetime type that causes it to skip the day after March 31st every year? What are your suggestions?

Further Reading:

• …

MySQL version 4.1 was quite revolutionary. The main reason for that was support for sub-queries.1

However since then MySQL users were rather discouraged to use that functionality, basically due to the implementation’s poor performance  and forced to build complicated queries based on joins rather than on subqueries.

Of course you can do some effort to optimize your subquery with sometimes very good results2. Not always it’s easy or even possible if you can’t change the code though.

You’d say it’s not a problem for typical OLTP, web based traffic at all, just don’t use subqueries! That’s true, …

O’Reilly authors get 10 copies of their own books for free, and my copies of the third edition of High Performance MySQL arrived yesterday. Now it’s official! It feels nice to actually hold it in my hand.

A few people have asked me about messages from Amazon saying that their ship date has changed. I don’t know anything about that; maybe Amazon just made a wild guess the first time and now they actually know something more realistic. Or maybe the book is more popular than expected? It’s currently at position #10 in the SQL category on Amazon, which seems pretty good to me. No “DaVinci Code” to be sure, but not bad for a technical book.

Further Reading:

• High …

I’ll be speaking at the Shenandoah Ruby Users Group Monthly Meetup tomorrow in Harrisonburg, Virginia. The topic is “Seven Things To Know About MySQL Performance.” See you there!

Further Reading:

• Speaking at the Shenandoah Ruby Users Group
• Central Virginia MySQL Meetup has reached cruising altitude
• New Ruby Conference: Ruby on Ales 2011
• …

Wow, I completely forgot to advertise this. I’m speaking Saturday (tomorrow) at RubyNation, which is already well underway (I’m missing the first day, though). My topic is “Seven Things To Know About MySQL Performance.”

Further Reading:

• I’m speaking at Surge 2011
• Speaking about Maatkit at CPOSC
• Speaking at Enterprise LAMP Summit 2009
• Speaking at Surge 2010
• …

While working through prepared statements in MySQL, there was an interesting MySQL regular expression question raised. A student wanted to know how to address the following error message:

ERROR 1139 (42000): Got error 'repetition-operator operand invalid' FROM REGEXP

They had substituted * for a .+ in a metasequence. A metasequence is a parenthetical expression that evaluates based on multiple alternative conditions, and the pipe (|) acts as an OR operator. The full code example is found on page 482 of the Oracle Database 11g & MySQL 5.6 Developer Handbook. The student’s change would have worked without an …