In MySQL significant performance improvements can be achieved by the correct use of indexes. It is important to understand different MySQL index implementations and one key improvement on indexes defined on single columns is to use multiple column or more commonly known concatenated indexes.

However it’s also possible to define ineffective indexes. This example shows you how to identify a concatenated index that is ineffective.

CREATE TABLE example (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  a  INT UNSIGNED NOT NULL,
  b  INT UNSIGNED NOT NULL,
  c  INT UNSIGNED NOT NULL,
  d  INT UNSIGNED NOT NULL,
  x  VARCHAR(10),
  y  VARCHAR(10),
  z  VARCHAR(10),
PRIMARY KEY (id),
UNIQUE INDEX (a,b,c,d)
) ENGINE=InnoDB;

INSERT INTO example(a,b,c,d) VALUES
(1,0,1,1),(1,0,1,2), (1,0,2,3), (1,0,4,5),
(2,0,2,1),(2,0,2,2), …

In a time when many tradeshows are experiencing lower then normal attendance the 8th Annual Southern California Linux Expo (SCaLE 8x) had record attendance this past weekend in Los Angeles. I was there exhibiting and conducting a community training day for Zenoss and was very impressed by not only the quality of the program but the enthusiasm of the attendees.

Here are some of the highlights:

The Mini Conferences

On the Friday before the main SCaLE expo and speaking program starts many people hold …

We’re pleased to announce that Blue Gecko is providing hosting and some technical support for Drizzle!  Drizzle is a community-driven project based on the popular MySQL DBMS that is focused on MySQL’s original goals of ease-of-use, reliability and performance.  For more information go to http://drizzle.org.

Related posts:

1. DBD::drizzle .301 released
2. Oracle, MySQL, MariaDB, and Drizzle. oh my.
3. Blue Gecko acquires ORA-600 Consulting

With the motivation from today’s public news on Twitter’s move from MySQL to Cassandra, my own skills desire following in-depth discussions at last November’s Open SQL Camp to consider Cassandra and yesterday’s discussion with a new client on persistent key-value store products, today I download installed and configured for the first time. Not that today’s news was unexpected, if you follow the Twitter Engineering Open Source projects you would have seen Cassandra as well as other products being used or evaluated by Twitter.

So I went from nothing to a …

I’ll be helping Morgan Tocker deliver the second half of his training course for MySQL Developers/DBAs in New York City in a few days (more Percona training). It was a snap decision at the last minute, but I’m hoping I’ll still get to meet some folks there. If we’ve corresponded over email or blog comments and you would like to get together, ping me in the comments here!

If you’re in the New York City area and you use MySQL, you should consider attending this course, too. Morgan knows his stuff and has written a good curriculum. Attendees give his courses excellent feedback, and the price is very reasonable. Oh, and I’ll be there too, did I mention that? You can pick my …

When you only have 140 characters to get your message across, you have to depend a lot on context. For Twitter, a big part of that context has become location. Knowing where someone is tweeting from can add a lot of value to the experience, and it's Raffi Krikorian's job to integrate location into Twitter. Raffi will be talking about this and other location-related topics at the upcoming Where 2.0 conference. We began by asking him how Twitter determines location, and whether it will always be an opt-in option.

Raffi Krikorian: I think part of it is based around the philosophy of Twitter itself. We only publish information that you've explicitly given to us on a tweet-by-tweet basis. So for location on your tweets, it's all opt-in. You have to give us that location information, and we'll put it out. There are other things we do behind the scenes, like our local …

I am writing because Sheeri sent me a note about a blog post written by Brian Aker, where Brian concludes, quite correctly, that (in Sheeri’s words not Brian’s)


MySQL is now just a branch (the official branch,
but a branch nonetheless, and a bunch of trademark (logo) and
copyright (docs) ownerships).


This is exactly true. No denying it. Why bother. It’s true. It’s also true for the vast majority of open-source projects, by the way.

I replied to Sheeri:


There's no denying that. The product direction will be set by whoever sets the best product management strategy backed by the most effective development effort. And there can be multiple winners.
-Paul


Well, this is the kind of quality output I can be relied on. It might not fit on twitter, but it’s …

I both love, and appreciate this quote from Monty:

"You know Bryan and should know, as all his friends does, that you can't influence what Bryan is saying; He is always speaking his own mind!"

Ignore the spelling of my name, Monty is not a native english speaker, and writing for him is a chore (which I can relate too, since writing to me is a chore as well... and to the people who I ask to edit what I write).

Larry McVoy, yes the author of Bitkeeper, once told me that while he worked at Sun that they had his workstation setup so that every post to Usenet, (or was it email in general?), got passed to a VP who would then decide if it was to be posted or not. My joke for the last few months has been "what, they only gave you a VP? I got a CTO!".

While at Sun, about every other week, either …

Tokutek is pleased to announce immediate availability of TokuDB for MySQL, version 3.0. It is designed for continuous querying and analysis of large volumes of rapidly arriving and changing data, while maintaining full ACID properties.

TokuDB v3.0 combines our long-standing performance advantages with ACID compliant transactional integrity:

• 10x-50x faster indexing for faster querying
• Full support for ACID transactions
• Fast recovery time (seconds or minutes, not hours or days)
• Immunity to database aging to eliminate performance degradation and maintenance headaches
• 5x-15x data compression for reduced disk use and lower storage costs

Because of its high indexing performance and transaction support, TokuDB is well suited to Web applications that must simultaneously store and query …

I pulled this from a quote on yesterday's Slashdot story about MySQL Licensing where the author of the quote mentions Drizzle's licensing terms:

"you require the code to be under BSD"

This is actually a myth, we don't.

If you look through the Drizzle codebase you will note that very few files have BSD headers, and all that do?

They are a part of new systems that have been written since the fork of the project, and not all of these are BSD.

Why is this?

A large part of Drizzle is derived work from MySQL, and in all cases there we inherited its GPLv2 license on files. Bug fixes and code refactoring projects all fall under the umbrella of "derived work". In all of those cases the work was made under the GPL but no copyright assignment ever occurred. To understand …