Here's another ode to a small but fundamental aspect of Oracle, following the same theme as The Humble IF Statement. This time, let's look at the COUNT( ) function. I think when you look at it the right way, it opens up the whole story about database performance.What's the first thing you do when poking around an unfamiliar system? I'll bet it involves SELECT COUNT(*) queries in one way or

With all the furor over MySQL 5.1 GA, its release schedule, its quality, etc etc I think a fundamental claim is being left un-examined. Lots of people are saying that if you leave out the new features in 5.1 and look only at the features that existed in 5.0, it’s better quality.

The implication is [...]

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 …