O’Reilly tells me that translation rights for our book, High Performance MySQL Second Edition have been purchased in the following languages:

Korean Russian Portuguese Polish Spanish Czech

Some of them were sold quite a while ago, and some are more recent. Portuguese was the first. I’m looking forward to learning all these languages (except for Spanish, which I already sort [...]

How would one concatenate strings from a column (multiple rows) into a single row using MySQL? I see its possible with MS SQL Server 2005 and above. Any incite into how to achieve this in MySQL would be much appreciated.

MS SQL Server 2005 - Example

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

SELECT Web_Account_ID,
GroupNameConcat = REPLACE(
        (
        SELECT
                Web_Account_Group_Name_ID AS [DATA()]
        FROM
                tblWebAccountGroup WAG
        WHERE
                WAG.Web_Account_ID = WA.Web_Account_ID
        ORDER BY
                Web_Account_Group_Name_ID
            FOR XML PATH ('')
        ), ' ', ',')
FROM tblWebAccounts WA
ORDER BY Web_Account_ID

Query Results Example

Source: …

The "splendid effort" to create an application from scratch in 6 hours. They did not succeed, but what a …

Download Maatkit

The December release is here! There are some goodies in this release, but the major one is an initial version of mk-log-parser, a slow log analysis tool that is carefully designed (with lots of input from Percona consultants) to make slow log analysis as productive and easy as possible. It’s based on a [...]

Selecting rows holding group-wise maximum is a favorite problem of mine, but one which only rarely pops up. But for some reason, after my last blog post on the subject, it seems to be mentioned almost daily around here.

Something that I forgot to mention in the previous post is that most of the examples there assume suitable indexing is available to get decent performance. Basically a composite index on both the column(s) in the GROUP BY and the column over which MAX is computed is needed. In the example I gave, such an index is available throught the primary key.

However, such an index may not be available in all cases. Maybe maintaining it would be too expensive, or maybe the data the max is computed over is itself the result of a (sub-)query, and no indexing is available. So it is worth it also to understand this case, as the performance of the …

If you were to measure MySQL’s “loadavg,” how would you do it?

The following metric (in pseudocode) is borrowed from Trevor Price at Aggregate Knowledge. It’s a way to calculate average query response time. What do you think of it?

$start = microtime(); $status1 = SHOW GLOBAL STATUS LIKE Questions; for ( 1 .. 100 ) { [...]

This is a quick informal poll. I’d like to know how much network traffic your server receives (not sends), in bytes per second. Give both avg and max if you have them. I’m especially interested in hearing about big, heavily loaded systems.

I’ll start things off: just by looking at a couple of [...]

I have written a new book on SQL DML. This is a total beginner book: how to commit and rollback, how to query, how to add data, etc.

Probably not of interest to most of the people who read this blog but if you know of anyone completely new to SQL, this would make a great Christmas present. Only 14.95. It is completely vendor agnostic, although the examples all use Oracle and MySQL.

You can view the Table Of Contents, Preface and Index here. I plan to release some of the chapters for free on the blog and will make the PDF of the book available at a discount. I have several more books like this (DDL, Intro to Relational Databases and Cloud Computing) under construction. I also plan to do some …

Today there was a question on the Freenode MySQL channel about a classical problem: Rows holding group-wise maximum of a column. This is a problem that I keep encountering every so often, so I thought I would write up something about it.

A good example of the problem is a table like the following holding versioned objects:

CREATE TABLE object_versions (
  object_id INT NOT NULL,
  version INT NOT NULL,
  data VARCHAR(1000),
  PRIMARY KEY(object_id, version)
) ENGINE=InnoDB

Now it is easy to get the latest version for an object:

SELECT data FROM object_versions WHERE object_id = ? ORDER BY version DESC LIMIT 1

The query will even be very fast as it can use the index to directly fetch the right row:

mysql> EXPLAIN SELECT data FROM object_versions
WHERE object_id = 42 ORDER BY version DESC …

MySQL just released their new MySQL Query Analyzer (link to a trial), and recently wrote up an interview with Mark Matthews about it. If you haven’t read that article, go ahead and do it. I have not used this software, but I fully believe its functionality is quite nice.

But there is at least [...]