In this post we’ll describe a query that accrued significant performance advantages from using a relatively long index key.  (This posting is by Zardosht and Bradley.)

We ran across this query recently when interacting with a customer (who gave us permission to post this sanitized version of the story):

SELECT name,
       Count(e2) AS CountOfe2
 FROM (SELECT distinct name, e2
        FROM (SELECT atable.NAME AS name,
                     pd1.NAME AS e2
               FROM atable INNER JOIN atable AS pd1
               ON  (atable.id = pd1.id)
               AND (atable.off = pd1.off)
               AND (atable.len = pd1.len)) ent
 WHERE ((ent.name<>ent.e2))) outside
 GROUP BY outside.name order by CountOfe2 desc;

With a table defined as

CREATE TABLE `atable` (
   `id` varchar(25) DEFAULT NULL,
   `off` bigint(20) DEFAULT NULL,
   `len` bigint(20) DEFAULT NULL,
   `name` …

Yesterday, I (Zardosht) posted an entry introducing clustering indexes.  Here, I elaborate on three differences between a clustering index and a covering index:

1. 
   
2. Clustering indexes can create indexes that would otherwise bounce up against the limits on the maximum length and maximum number of columns in a MySQL index.
   
3. Clustering indexes simplify syntax making them easier and more intuitive to use.
   
4. Clustering indexes have smaller key sizes leading to better performance.
   

Expanding MySQL’s Limits

MySQL allows at most 16 columns in an index and at most 3072 bytes per index. For tables that have more than 16 columns or a row size of greater than 3072 bytes, one cannot create a covering index that includes …

Following the launch of the Open Database Alliance some people have assumed that it is only a matter of time before MariaDB becomes the de facto replacement for MySQL.

That assumes that Oracle will allow the development of MySQL to stagnate, either deliberately or through neglect - something that we have expressed our doubts about, but even if that were the case it appears that the GPL (or more to the point MySQL’s dual licensing strategy) may restrict the potential for MariaDB.

Curt Monash recently raised the question of whether closed-source storage engines can be used with MySQL (and, by …

Day two of the conference was a little disappointing, as far as sessions went. There were several time blocks where I simply wasn’t interested in any of the sessions. Instead, I went to the expo hall and tried to pry straight answers out of sly salespeople. Here’s what I attended.

Paying It Forward: Harnessing the MySQL Contributory Resources

This was a talk focused on how MySQL has made it possible for community members to contribute to MySQL. There was quite a bit of talk about IRC channels, mailing lists, and the like. However, the talk gave short shrift to how MySQL plans to become truly open source (in terms of its development model, not its license). I think there was basically nothing to talk about there. I had a good conversation about some of my concerns with the speaker and some others from MySQL right afterwards.

There was basically nobody there — I didn’t count, but I’d say maybe 10 or 12 people. I …