Pythian Group

I was reading a brilliant book Relational Database Index Design and the Optimizers by Tapio Lahdenmaki and Mike Leach. At the end of one of the chapters I came across the exercise to design two indexes for a given query: one to minimize the number of index rows scanned and second one to eliminate sorting.

Using algorithm described in the book I quickly came up with two indexes and while first one looked fine, I was really confused by the second one for the elimination of the sort. Let me show an example, not copy one from the book, but rather show a test I did with MySQL.
Read the rest of this entry . . .

[yanick@enkidu shuck]$ perl -MFile::Find::Rule -MFile::Slurp=slurp \
    -E'say slurp $_ for File::Find::Rule->file->name("news")->in(".")'

In a turn of events so monumental that it can only possibly be a sign that Ragnarok is nigh upon us, an early adopter implementation of Perl 6 has been released. The number of blog entries that it generated is, as one might expect, quite massive. But most important is, the downloads — both for Unix and Windows — are available on Github. Don’t lose any time! Go, download, compile, and get a taste of what the new kid on the block has to offer.

Read the rest of this entry . . .

In How to tune MySQL’s sort_buffer_size, Baron gives a condescending viewpoint on how to tune the sort_buffer_size variable in MySQL. In a much-nicer-nutshell, his advice is “do not change sort_buffer_size from the default.”

Baron did not explain the logic behind his reasoning, he handwaves that “people utterly ruin their server performance and stability with it,” but does not explain how changing the sort_buffer_size kills performance and stability. Regardless of how respected and knowledgeable the source, NEVER take any advice that tells you what to do or how to do it without understanding WHY.

This article will explain the “why” of Baron’s point, and it will also talk more about understanding why, an integral part against the “Battle against any guess.” Baron’s recommendation to leave sort_buffer_size as the default is just as bad as all the advice given to change the sort_buffer_size, because all that advice (including Baron’s) does not explain the underlying causes.

First, I explain the sort_buffer_size issue. Read the rest of this entry . . .

Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).

There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes:

“querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.”

Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.

Read the rest of this entry . . .

This is the 150th edition of Log Buffer, the weekly review of database blogs. Someone accidentally left Dave Edwards’ cage unlocked, and he escaped, thus leaving me with the pleasurable duty of compiling the 150th weekly Log Buffer.

Many people other than Dave are finding release this week. Read the rest of this entry . . .

At the January 2009 Boston User Group I presented a session on the new partitioning feature in MySQL 5.1. I go through how to define partitions, how partitioning makes queries faster, the different types of partitioning and when to use each type, and the restrictions and limitations of partitioning.

The slides are available at http://www.technocation.org/files/doc/2009_01_Partitioning.pdf. The video is embedded at the bottom of this post, and is also available at youtube at http://www.youtube.com/watch?v=zvN9XI-FraI.

Notes:
Read the rest of this entry . . .

At the 2008 MySQL Conference and Expo, The Pythian Group gave away EXPLAIN cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.

For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf

* or tea, for those of us in the civilized world.