Developer Day MySQL - Minneapolis

Another MySQL Developer Day is available. I will be joining the MySQL team in Minneapolis Thursday, November 17, 2011.

You can register here.

Colorado MySQL meetup group

I just started a Colorado MySQL meetup group. This is long overdue !

If your in the Colorado area feel free to join and we can work on getting some meetings started.

http://www.meetup.com/Colorado-MySQL-Meetup-Group/

http://www.facebook.com/#!/groups/mysqlcolorado/

Explain....

Explain.... It is a very simple command that I feel is one of the most overlooked commands by new MySQL users. It is also a very valuable command available for MySQL. I realize I am preaching to the choir for a lot of MySQL users. However, for everyone who uses explain, we are bound to have many who do not. 

The MySQL documentation on this is great and available here and Optimizing Queries with EXPLAIN
Developer and a dba issues will continue for years,  but we can at least start on a level playing field. When writing a query, regardless of what it is, it is a good practice is to start it with explain first. This can achieve a couple things for you.

• It checks your syntax to help you avoid mistakes.
• Allows you to display information from the optimizer

Using the world example data for avoiding mistakes via explain. (innodb version)

http://dev.mysql.com/doc/index-other.html

mysql [localhost] {root} (world) > explain extended SELECT City.CountryCode , City.Name , Country.Name , Country.Capital , Country.Population FROM City , Country WHERE Country.Continent = 'North America' AND City.CountryCode = Country.Code;

+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+----------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+----------+-------------+

| 1 | SIMPLE | Country | ALL | PRIMARY | NULL | NULL | NULL | 200 | 100.00 | Using where |

| 1 | SIMPLE | City | ref | CountryCode | CountryCode | 3 | world.Country.Code | 18 | 100.00 | |

+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+----------+-------------+

2 rows in set, 1 warning (0.00 sec)

This query does work but I would never write it this way. It works for such a small data set but a join would work better.

mysql [localhost] {root} (world) > explain extended SELECT C.CountryCode , C.Name , Y.Name , Y.Capital , Y.Population

-> FROM City C

-> INNER JOIN Country Y ON Y.Code = C.CountryCode ; WHERE Y.Continent = 'North America' ;

+----+-------------+-------+------+---------------+-------------+---------+--------------+------+----------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------+---------------+-------------+---------+--------------+------+----------+-------+

| 1 | SIMPLE | Y | ALL | PRIMARY | NULL | NULL | NULL | 200 | 100.00 | |

| 1 | SIMPLE | C | ref | CountryCode | CountryCode | 3 | world.Y.Code | 18 | 100.00 | |

+----+-------------+-------+------+---------------+-------------+---------+--------------+------+----------+-------+

2 rows in set, 1 warning (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Y.Continent = 'North America'' at line 1

mysql [localhost] {root} (world) >

An error! Explain found this simple typo. While updating query to use a join, I adjusted my table references to aliases and a semicolon was placed before the where. Simple typo, but in the real world it could have been worse. What if your typo, enabled a full table scan across a table with billions of rows of data? Real world issues are usually related to “such an easy query” issues, so it is rushed. Then code gets pushed and nothing worked as planned. We can all write, clean, effective, and optimized queries, if we pay attention and understand what we are executing.

So error fixed .

mysql [localhost] {root} (world) > explain extended SELECT C.CountryCode , C.Name , Y.Name , Y.Capital , Y.Population

-> FROM City C

-> INNER JOIN Country Y ON Y.Code = C.CountryCode WHERE Y.Continent = 'North America' ;

+----+-------------+-------+------+---------------+-------------+---------+--------------+------+----------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------+---------------+-------------+---------+--------------+------+----------+-------------+

| 1 | SIMPLE | Y | ALL | PRIMARY | NULL | NULL | NULL | 200 | 100.00 | Using where |

| 1 | SIMPLE | C | ref | CountryCode | CountryCode | 3 | world.Y.Code | 18 | 100.00 | |

+----+-------------+-------+------+---------------+-------------+---------+--------------+------+----------+-------------+

2 rows in set, 1 warning (0.01 sec)

Using the employee example data to display information from the optimizer via explain. (innodb version)

http://dev.mysql.com/doc/index-other.html

The real use and best use of explain is to “display information from the optimizer about the query execution plan.” (refman) When executing joins across tables you need to understand what your pulling and make sure the best indexes are being used.

mysql [localhost] {root} (employees) > explain SELECT e.first_name , e.last_name , e.gender , e.hire_date , t.title , s.salary

FROM employees e

INNER JOIN titles t ON t.emp_no = e.emp_no AND e.hire_date BETWEEN t.from_date and t.to_date

INNER JOIN salaries s ON s.emp_no = e.emp_no AND e.hire_date BETWEEN s.from_date and s.to_date

WHERE e.gender='M'

ORDER BY e.hire_date ASC;

+----+-------------+-------+------+----------------+---------+---------+--------------------+--------+----------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+----------------+---------+---------+--------------------+--------+----------------+

| 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | NULL | 300030 | Using filesort |

| 1 | SIMPLE | t | ref | PRIMARY,emp_no | PRIMARY | 4 | employees.e.emp_no | 1 | Using where |

| 1 | SIMPLE | s | ref | PRIMARY,emp_no | PRIMARY | 4 | employees.t.emp_no | 4 | Using where |

+----+-------------+-------+------+----------------+---------+---------+--------------------+--------+----------------+

150291 rows in set (3.88 sec)

300030 rows ? NULL KEY ?  ick.

mysql [localhost] {root} (employees) > ALTER TABLE employees ADD KEY gender (gender);

mysql [localhost] {root} (employees) > explain SELECT e.first_name , e.last_name , e.gender , e.hire_date , t.title , s.salary

FROM employees e

INNER JOIN titles t ON t.emp_no = e.emp_no AND e.hire_date BETWEEN t.from_date and t.to_date

INNER JOIN salaries s ON s.emp_no = e.emp_no AND e.hire_date BETWEEN s.from_date and s.to_date

WHERE e.gender='M'

ORDER BY e.hire_date ASC;

+----+-------------+-------+------+----------------+---------+---------+--------------------+--------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+----------------+---------+---------+--------------------+--------+-----------------------------+

| 1 | SIMPLE | e | ref | PRIMARY,gender | gender | 1 | const | 150015 | Using where; Using filesort |

| 1 | SIMPLE | t | ref | PRIMARY,emp_no | PRIMARY | 4 | employees.e.emp_no | 1 | Using where |

| 1 | SIMPLE | s | ref | PRIMARY,emp_no | PRIMARY | 4 | employees.t.emp_no | 4 | Using where |

+----+-------------+-------+------+----------------+---------+---------+--------------------+--------+-----------------------------+

3 rows in set (0.00 sec)

Now I am not doing a full table scan but using the index on gender and only across 15k rows.

Explain shows this information so we can adjust when required.

These are simple examples I realize. Adding an index also has to be reviewed and just not put on everything.

Take the time to use explain. Make it a habit.

Other blog posts about explain over the years:

http://www.mysqlperformanceblog.com/2006/07/24/extended-explain/

http://mysql-tips.blogspot.com/2005/04/mysql-explain-example.html

http://www.xaprb.com/blog/2007/07/29/introducing-mysql-visual-explain/

http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm
http://explainextended.com/2010/11/03/10-things-in-mysql-that-wont-work-as-expected/
http://www.mysqlperformanceblog.com/2006/07/24/mysql-explain-limits-and-errors/

MySQL Community Reception at Oracle OpenWorld

Save the date ! Tuesday, October 04, 2011 7:00 PM – 9:00 PM

You’re all invited to Oracle’s MySQL Community Reception. This event is open to everyone. No OpenWorld registration is required to attend.

Register Now !

MySQL Developer Day in DC

Many thanks to all of those from MySQL Community who attended the Washington DC Developer day.

We had great questions from a full room of community users and we look forward to any feedback that you might have.

I look forward to the next one ! 

Excitement at Goscon

The Goscon event was cut short today due to the earthquake. The 5.9 quake was felt as we had the MySQL team was supporting the booth. Nothing like a little more excitement to awaken the show floor.

All was not lost, it was good to see and talk with Baron Schwartz from Percona.

The MySQL Developer Days are still planned for tomorrow .

Show your support for MySQL Workbench !

The 2011 DevProConnections CommunityChoice Awards has two categories that include nominations for MySQL Workbench !

You can support MySQL Workbench with your vote. The two categories that include Workbench are, “Component Set” (page 1 # 7) and “IDE” (page 2 #14).

The survey is available here and information about workbench is always available here: http://www.mysql.com/products/workbench/

Future of Open Source

Back in 1995 we had the Initial releases of the Apache Server, MySQL and PHP. Three major open source projects that still power the web today. Majority of them running on GNU/Linux platforms.

Apache then soon also added Tomcat as well to the mix to further enhance this powerful combination. So what is next?

Now that we are well into the 21^st century, what will be the next stable projects of the Internet? What will change how we use the Internet? Certainly we have great projects like phpMyAdmin, Zimbra, Drupal, Joomla, Wordpress, SugarCRM, Mozilla and OpenOffice, osCommerce and memcached. I am sure I missed some.  I am not discrediting any of these projects, I have used and supported them. I am more curious about what we all think is the next big thing to change the way we relate and present things on the Internet. Will Cloudera , Owncloud , VoltDB, Postgresql, or OpenStack change how we do things ? Will the mobile market be revolutionized by open source software? Will cloud computing be the answer?

Time will tell but I am very curious about others opinions.

MySQL 5.6 Feedback

To follow up on a post by Dave --

The MySQL Community Team would like your feedback about MySQL 5.6 ! What are you excited to see and test? What have you tested and benchmarked? Did you previously see the advantages of going to MySQL 5.5 and now eager to try 5.6? What are your favorite new features: Full Text Search InnoDB tables, Binary Log API, Binary Log Group Commit, multi-thread slaves, memcached-to-InnoDB? Feel free to email keith.larson[@]oracle[.]com with feedback.

If you have been busy with development, and not realized that previews of MySQL 5.6 are available, then now is your chance. Previews of MySQL 5.6 are available at http://labs.mysql.com for you to evaluate.

Innovation Nation & GOSCON 2011

I just got a pile of MySQL shirts and stickers for the Goscon show!


The MySQL team will showcase the latest product developments and demos running on a Sun Ray at the Exhibition Hall, and we look forward to meeting you there! 


For more information:


http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=140023&src=7314534&src=7314534&Act=78