With MySQL 5.6 just gone GA, I thought it would be good to take a look at the changes in options and variables that comes with the new release.

First of all, several of the existing options have get new default values. As James Day already have written a good post about that in his blog, I will refer to that instead of going through the changes. For a general overview of the new features and improvements, the recent blogs by Rob Young and Peter Saitsev are good starting points together with the …

In relation to these two posts from Justin Swanhart and Anders Karlsson about transaction isolation levels, I thought it was interesting to do a little survey to get an idea of the most commonly used isolation levels.

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll. Related Posts :

• [UPDATED] Why you should go to Percona Live (London) ?
• Fatal timeout !
• …

When I have discovered MySQL User-defined variables at the first time, I didn’t pay much attention to them and didn’t realize their strength. At first, one can think that they are just one of the many unnoticed MySQL features. Well, I was wrong. Here, I will try to summarize their strength, though I think there is […]

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).

In MySQL5 MySQL implemented changes so you can view global status (counters) and session counters. In 5.1 MySQL extended this and implemented this information in the INFORMATION_SCHEMA.GLOBAL_STATUS and INFORMATION_STATUS.SESSION_STATUS tables.

Global status

mysql> SELECT * FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'COM_SELECT'\G*************************** 1. row *************************** VARIABLE_NAME: COM_SELECTVARIABLE_VALUE: 2076641 row in set (0.02 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Com_select';+---------------+--------+| Variable_name | Value |+---------------+--------+| Com_select | 207664 | +---------------+--------+1 row in set (0.03 sec)
Session status
mysql> SHOW SESSION STATUS LIKE 'Com_select';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_select | 13 | +---------------+-------+1 row in set (0.02 sec)

mysql> …

MySQL instance running an Infobright engine


mysql> explain SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | ac | ALL | NULL | NULL | NULL | NULL | 124426 | |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 7594256 | |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
2 rows in set (0.01 sec)

mysql> SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a ON ac.UID=a.UID;
+---------------+
| COUNT(ac.UID) |
+---------------+
| 17466 |
+---------------+
1 row in set (0.23 sec)

…

Today’s contradiction:

MySQL has server variables named new and old.

The new variable can be set per-session and globally, and is dynamic. The old variable is not dynamic, and only global in scope. Both default to FALSE in MySQL 5.1.

According to the manual, the new variable:

was used in MySQL 4.0 to turn on some 4.1 behaviors, and is retained for backward compatibility.

That same page notes the following about the old variable:

when old is enabled, it changes the default scope of index hints to that used prior to MySQL 5.1.17. That is, index hints with no FOR clause apply only to how indexes are used for row retrieval and not to resolution of ORDER BY or GROUP BY clauses.

That’s right — the …

Do you have_community_features? I do!

SHOW GLOBAL VARIABLES LIKE 'have_community_features';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| have_community_features | YES   |
+-------------------------+-------+
1 row in set (0.00 sec)

I am pretty sure this is one of those variables that MySQL has put in as an unused placeholder, but for now, it is not even documented as unused (as are table_lock_wait_timeout, http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html, and a Google search restricted to the site dev.mysql.com turns up only one match, …

I've come up with some interesting workarounds for missing features using @session variables and I'd like to share one with you today: DELETE ... JOIN ... LIMIT N;

okay, so we've got two tables, one with many duplicates and one with no duplicates.
We want to join the tables using 'id', but only want to delete N duplicates from t1.
MySQL's DELETE does not include LIMIT support when JOIN is used, so we need to work around
that using some fancy footwork:


mysql> select * from t1;
+------+------+
| id | abc |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
+------+------+
8 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | xyz |
+----+------+
| 1 | 1 |
+----+------+
1 row …