MariaDB announced their 5.3 GA and 5.5 alpha releases.
Oracle will be at DrupalCon in Denver, CO from Monday, March 19th through Friday, March 23rd.
Percona's XtraDB cluster announcement
XtraDB Cluster at the San Francisco MySQL Meetup on Wednesday, March 21st
There are a lot of events scheduled for the next few months, we are excited about the international scope and the mix of big and small, paid and free events. This week we finish explaining the output of EXPLAIN. Ear candy is sshfs, at the movies is a presentation on joins and subqueries and how to optimize them.
Free OTN MySQL Developer Day, Singapore, Tuesday Feb 21st
Free OTN MySQL Developer Day, Paris, France, Tuesday Feb 21st
Registration for Percona Live: MySQL Conference and Expo is open! The conference is from Tuesday, April 10th through Thursday, April 12th at the Santa Clara, CA convention center. Early bird pricing ends March 12th, 2012.
Use code PL-pod and save 10% off the early bird prices!. Check out the tutorial and session schedule!
SkySQL and MariaDB Solutions Day on Friday, April 13th, 2012 in Santa Clara, CA.
Registration for Percona Live: MySQL Conference and Expo is open! The conference is from Tuesday, April 10th through Thursday, April 12th. Early bird pricing ends March 12th, 2012.
Use code PL-pod and save 10% off the early bird prices!.
The 1st Latin American Conference about MySQL, NoSQL and Cloud technologies will be held in Buenos Aires in June. It is called the MariaDB NoSQL & Cloud Latin American Conference and we'll bring you more information as it becomes available.
When examining the execution plan of troublesome queries in MySQL, most users are aware of using EXPLAIN. However, an often overlooked, yet very helpful extension of EXPLAIN, is EXPLAIN EXTENDED coupled with the SHOW WARNINGS command.
The reason being is because it provides a little more information about how the optimizer processes the query, and thus it could help to quickly identify a problem that you might not otherwise recognize with just EXPLAIN.
For instance, here is a common query which could be inefficient:
SELECT id FROM t WHERE id='1';
And here is the CREATE TABLE output:
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` decimal(10,0) default NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
You can see it’s a very basic table, and a very basic query.
And looking at the EXPLAIN output, everything still appears normal:
mysql> explain select id from t where id='1'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t | ref | id | id | 6 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
However, now let’s look at the EXPLAIN EXTENDED and SHOW WARNINGS output:
mysql> explain extended select id from t where id='1'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t | ref | id | id | 6 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------+ | Note | 1003 | select `testing`.`t`.`id` AS `id` from `testing`.`t` where (`testing`.`t`.`id` = _latin1'1') | +-------+------+----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
In the WHERE clause of the SHOW WARNINGS output, you see this:
where (`testing`.`t`.`id` = _latin1'1')
The _latin1′1′ is key, as it shows the value of 1 is getting cast as a latin1 value. This is because we surrounded the 1 with single quotes in the query.
Note that this does not happen if you do not surround the 1 with single quotes:
mysql> show warnings; +-------+------+-------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------+ | Note | 1003 | select `testing`.`t`.`id` AS `id` from `testing`.`t` where (`testing`.`t`.`id` = 1) | +-------+------+-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
In this example, the constant value of 1 is being cast, which is not horrible, but still not as efficient as it should be. However, depending on how the query is written, I’ve seen where it might choose to cast/convert the row retrived from the database (especially in a JOIN scenario - it will need to convert one column if they differ). These conversions or casts can be very inefficient (especially if within a JOIN or worse, a sub-query), and would not be otherwise caught without using EXPLAIN EXTENDED / SHOW WARNINGS.
And in addition to the above example, it is common to see similar types of conversions occurring if you try to match two columns whose character sets differ. This can be another easy-to-overlook problem, which can lead to poor performance, and you wouldn’t know it wasn’t running efficiently unless you used EXPLAIN EXTENDED (or unless it brings your system to a halt during a heavy load ..).
So in conclusion, consider using EXPLAIN EXTENDED / SHOW WARNINGS in lieu of just EXPLAIN when analyzing your queries.
Note: If you are using pre-5.1.46, you may want to consider running both EXPLAIN and EXPLAIN EXTENDED to ensure the optimization plans match. This is due to the following bug which existed:
http://bugs.mysql.com/bug.php?id=47669