A lot has happened in MySQL 5.6 for queries joining many tables. For the most common use cases we have drastically reduced the cost of finding the execution plan. We have also improved the heuristics and removed bugs so that the final plan is often better than it used to be. Read on if you are one of those people who do 15 way joins!

Finding a query execution plan
First some background. You can skip this part if you know how MySQL picks the table join order in 5.5.

When presented with a query, MySQL will try to find the best order to join tables by employing a greedy search algorithm. The outcome is what we call a query execution plan, QEP. When you join just a few tables, there's no problem calculating the cost of all join order combinations and then pick the best plan. However, since there are (#tables)! possible combinations, the cost of calculating them all soon becomes too high: for five tables, e.g., …

Continuent today announced the new Tungsten Enterprise 1.5. Tungsten Enterprise moves beyond simple big data by managing increasing data volume, complexity, speed and concurrency in the cloud or on-premise. Tungsten Enterprise pushes the envelope in multi-master, multi-site MySQL database clusters with the following new features:Set up Tungsten Enterprise clusters in the Amazon cloud with a

Note: this article was originally published on http://blogs.innodb.com on April 10, 2012 by Calvin Sun.

InnoDB team is pleased to announce the 2012 Spring labs release, with several much anticipated new features and performance enhancements. Please download mysql-5.6-labs-april-2012 from MySQL Labs and give a try. Do not forget to provide your feedback.

The 2012 Spring labs release on MySQL Labs consists of the following InnoDB new features, which are not in the newly released MySQL 5.6.5 DMR yet:

• Online DDL: some of the DDLs are now truly online, including ADD INDEX, SET DEFAULT, and DROP FOREIGN KEY.
• Memcached plugin: with additional features, such as SASL support.
• Transportable …

Attached are slides and a excercise spreadsheet for the How to evaluate which MySQL High Availability solution best suits you tutorial.

We will use the spreadsheet after the break, please download it now.

Here are some quick and easy bash commands to solve every day problems I run into. Comment and leave some of your own if you like. I might update this post with new ones over time. These are just some common ones.

Iterate through directory listing and remove the file extension from each file
ls -1 | while read each; do new=`echo $each |sed 's/\(.*\)\..*/\1/'` && echo $new && mv "$each" "$new"; done

Output relevant process info, and nothing else
ps axo "user,pid,ppid,%cpu,%mem,tty,stime,state,command"| grep -v "grep" | grep $your-string-here

Setup a SOCKS5 proxy on localhost port 5050, to tunnel all traffic through a destination server
ssh -N -D 5050 username@destination_server'

Setup a SOCKS5 proxy via a remote TOR connection, using local port 5050 and remote TOR port 9050
ssh -L 5050:127.0.0.1:9050 username@destination_server'

Display text or code file contents to screen but don't display any # comment lines
sed -e '/^#/d' $1 < $file_name_here …

How to check if any MySQL memory has been swapped out? This post explains it.

Check if system is currently using any swap:

server ~ # free -m
             total       used       free     shared    buffers     cached
Mem:          3954       2198       1755          0        190       1040
-/+ buffers/cache:        968       2985
Swap:         3906          0       3906

In the above example swap is not in use, so no further checks would be necessary.

However if free command would report some usage, how to check whether MySQL memory was swapped out or not?

It is not possible to determine that using standard tools such as ps or top. They will report various memory related information per each process, but no clear indication whether something is in RAM or in swap space. But it is possible with this trivial command:

awk '/^Swap:/ { SWAP+=$2 } END { print …

The Wall Street Journal’s Heard on the Street column this morning was oddly titled Oracle’s Little Issue With Big Data. The point of the article is that data is exploding but the WSJ does not see how Oracle is going to take advantage of that explosion. But I found it odd that WSJ has missed the point!

At a customer conference in Tokyo last week, Oracle President Mark Hurd noted how digital information is growing exponentially, from 1.8 zettabytes last year to an expected 35 zettabytes in 2020. If an 11-ounce cup of coffee represented one gigabyte, according to a Cisco Systems report, then one zettabyte would have the same volume as the Great Wall of China. Everything from digital videos to emails to health records is fueling this.

It seems that the Journal has discovered that people …

I’m delighted to announce a partnership with New Relic. The press release has the details (all new Gold and Platinum support contracts get free New Relic subscriptions) but I wanted to add some of the behind-the-scenes context.

The quote from me in the press release is a real quote: I really have been telling people for years that they should use New Relic. I even wrote it in the new edition of High Performance MySQL.

Why? Simple. People who use New Relic are more able to self-service. And anytime I can point people to the best solution for them, …

The April 2012 InnoDB labs release introduces a new feature in InnoDB that allows you to choose the location of specific tables.  For example, you can place critical tables onto an SSD drive while leaving the system tablespace on a hard drive.  Conversely, you can store you primary database files on an SSD and put a seldom used but very large archive or reference table on a larger cheaper hard drive.

Innodb now makes use of the following existing syntax in MySQL ;

CREATE TABLE  . . .  DATA DIRECTORY = ‘absolute path of data directory’;

CREATE TABLE  . . .  PARTITION . . . DATA DIRECTORY = ‘absolute path of data directory’;

This syntax is used in MyISAM and Archive engines to make use of symbolic links in those operating systems that support it.  But InnoDB can use this syntax on any OS since it stores the path in a new system table called SYS_DATAFILES.  There is also …

In a previous post, I analyzed how a query of the famous DBT3 benchmark was
optimized by MySQL. It was this query, named "Q16" in the DBT3 jargon:

select
 p_brand,
 p_type,
 p_size,
 count(distinct ps_suppkey) as supplier_cnt
from
 partsupp,
 part
where
 p_partkey = ps_partkey
 and p_brand <> 'Brand#23'
 and p_type not like 'LARGE PLATED%'
 and p_size in (43, 1, 25, 5, 35, 12, 42, 40)
 and ps_suppkey not in (
  select
   s_suppkey
  from
   supplier
  where
   s_comment like '%Customer%Complaints%'
 )
group by
 p_brand,
 p_type,
 p_size
order by
 supplier_cnt desc,
 p_brand,
 p_type,
 p_size;

Here is a brief recap of conclusions I had drawn:

• for this query, MySQL tranforms the IN condition to EXISTS and then
  evaluates it with the "unique_subquery" technique, which does an …