I wanted to share a little rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it's generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those for the sake of simplicity.

Let's start with this query, which returns zero rows but does a full table scan. EXPLAIN says there are no possible_keys.

PLAIN TEXT SQL:

1. SELECT * FROM tbl WHERE STATUS='waiting' AND source='twitter'
2.  AND no_send_before <= '2009-05-28 03:17:50' AND tries <= 20
3.  ORDER BY date ASC LIMIT 1;

Don't try to figure out the meaning of the query, because that'll add complexity to the example In the simplest case, we want to put the most …

Spring forward. Freeloaders, leeches and hermits. Intel buys Wind River. And more.

Follow 451 CAOS Links live @caostheory

A Spring in its step
SpringSource boasted of rapid revenue growth while CEO Rod Johnson claimed that Red Hat’s Open Choice initiative is defensive response to SpringSource, a suggestion that was denied by Rich Sharples.

Freeloaders, leeches and hermits
I already provided my views earlier this week on Infoworld’s report about open source ‘leeches’ and corporate contributions. The debate continued as Dave Rosenberg …

Devart has released today the improved version 1.10 of dbForge Schema Compare for SQL Server, a sophisticated tool specially designed to meet your specific comparison tasks, help analyze schema differences at a glance, and synchronize them correctly, saving time and efforts.

dbForge Schema Compare for SQL Server 1.10 features the following enhancements:

• Faster comparison of database objects
  The tests proved that dbForge Schema Compare for SQL Server 1.10 compares databases twice faster than the previous version did. This improvement is of great value especially for comparison of large databases.

  For example, Schema Compare for SQL Server 1.10 compared databases located on SQL Server 2008 and containing 958 tables, 60 views, 3736 stored procedures, and 469 …

I performed a new set of tests of the patch to split the
rollback segment mutex on Linux. All these tests gave
positive results with improvements in the order of 2%.

One could also derive from the results some conclusions.
The first conclusion is that this split mainly improves
things when the number of threads is high and thus
contention of mutexes is higher. At 256 threads a number
of results improved up to 15%.

The numbers on lower number of threads were more timid
although in many cases an improvement was still seen.

What was also noticeable was that the sysbench read-write
with less reads which makes the transactions much shorter
the positive impact was much greater and the positive
impact on long transactions was much smaller (+0.4%
versus +2.5%). The impact on the short transaction test
with less reads was very positive also on …

Spent some time last night to split apart the big patch of improvements that we have added to MySQL Server 5.1 over the years. It's now 8 patches, and looks like there going to be at least three more on top of this.

The patches are organized as a series of quilt patches which I pushed to launchpad with bazaar. They can be browsed online http://bazaar.launchpad.net/~msvensson/+junk/mysql-cluster-patches/files or use "bzr branch lp:~msvensson/+junk/mysql-cluster-patches" . Currently they apply to mysql-5.1.34, but I'll try to keep them updated as we merged in future versions of MySQL Server

1. …

Hi all,

I created the perfect AMI: OpenSolaris with JDK, Ant, MySQl 5 and GlassFish. And then it turns out that OpenSolaris has different directions for bundling than Linux. It took me a while to find them, but I did. Here they are (I'll skip the pre-bundling cleaning up):

1. Connect to the OpenSolaris instance in the same way as Linux (SSH).
2. Execute the following commands: cd /mnt and /opt/ec2/sbin/rebundle.sh -v your-bundle.img. Note: of course you can name the bundle whatever you want.
3. Get some coffee. This could take a while.
4. Set the following environment variables:
   • export BUCKET=<bucket-name>
   • export JAVA_HOME=/usr/jdk/latest
   • export EC2_HOME=/opt/ec2
   • export PATH=$PATH:$EC2_HOME/bin …

Hi all,

I created the perfect AMI: OpenSolaris with JDK, Ant, MySQl 5 and GlassFish. And then it turns out that OpenSolaris has different directions for bundling than Linux. It took me a while to find them, but I did. Here they are (I'll skip the pre-bundling cleaning up):

1. Connect to the OpenSolaris instance in the same way as Linux (SSH).
2. Execute the following commands: cd /mnt and /opt/ec2/sbin/rebundle.sh -v your-bundle.img. Note: of course you can name the bundle whatever you want.
3. Get some coffee. This could take a while.
4. Set the following environment variables:
   • export BUCKET=<bucket-name>
   • export JAVA_HOME=/usr/jdk/latest
   • export EC2_HOME=/opt/ec2
   • export PATH=$PATH:$EC2_HOME/bin …

I’m happy to note (this is internal Open Query happiness but I’m pleased to share) that so far we have a 100% renewal rate for our Proactive Services for MySQL subscriptions. Some of the early clients have grown in the initial period and are have now moved to a higher # of hours (this can also be changed upward during a term), which is of course excellent both for the clients and for us.

I was in eager anticipation of this time since the introduction of the concept late last year, as it is of course the essential proof of whether a subscription service actually works over time. Ideally, you’d want renewal to be a simple straightforward process, with the client having experienced the value of the service. This is relatively straightforward in this case, since it’s not an insurance, emergency or retainer type arrangement – the client actually gets benefits each and …

I’ve decided to start writing a book about Maatkit. The working title is Using Maatkit. The goal is to provide another angle onto the tools; the docs are quite good in my opinion, but they only say what the tools do. The book will tell you how to use the toolkit to accomplish tasks.

I have no clue when it’ll be done. There is no schedule. At the current rate, it’ll take a while. I posted an outline to the Maatkit mailing list.

I have a few goals for the project. I want it to be printable-quality material (with an index and professional copyediting, which I have already figured out will cost me a pretty penny), but I also want to be able to update it quickly. I’ve had a number of discussions about this idea over the last year with folks in the …

MySQL (Sun, Oracle?) is hosting a Webinar “Guide to Scaling OpenLDAP with MySQL Cluster” on June 24th at 10:00AM PST. Howard Chu, CTO of Symas Corporation, will be the primary technical presenter. This is going to be a repeat of the great pitch we did at the MySQL Conference in Santa Clara. This is a [...]