Focus on MySQL | Oracle OpenWorld 2011

Oracle OpenWorld is just a few days away now!

As a reminder, you can attend all of the Sunday MySQL sessions organized by IOUG and the MySQL community with a full OOW pass.

The MySQL community reception is also available to everyone on Tuesday .

See you soon !

Workbench Scripts

A was checking out the new Workbench today after I saw this blog post http://wb.mysql.com/?p=1169

It all worked great and very easy.  Nice clean code that helps out the PHP Developer.
example:
$host="localhost";
$port=3306;
$socket="";
$user="";
$password="";
$dbname="";

$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
    or die ('Could not connect to the database server' . mysqli_connect_error());

//$con->close();


$query = "SELECT * FROM exampledb";


if ($stmt = $con->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($field1, $field2);
    while ($stmt->fetch()) {
        //printf("%s, %s\n", $field1, $field2);
    }
    $stmt->close();
}

While this is a simple example it is a fantastic way for new developers to get started and learn how things are done.

A DBA can easily write a complex query and hand over PHP code to the developer. Nice work!

The ability to then write your own plugins really opens it up for some advanced developers. 

This is a step in the right direction and supports working together between DBA and developer.

MySQL Oracle Open World Session Calendar

With 47 MySQL sessions at Oracle Open World this year we are going to be busy. Keeping updated on them in a easy fashion will be best for all of us.

Sheeri has a matrix here: http://technocation.org/files/doc/2011_OOW_MySQL_Content.html  

Oracle as a PDF here : http://www.oracle.com/openworld/oow11-focuson-mysql-486114.pdf

I went ahead and made public calendars so we can sync this information across our phones and etc....  I will update them when I find out  about any changes and then of course it should sync out to all of you who use them.


SESSIONS

XML https://www.google.com/calendar/feeds/pj443o2ojeggqfofr82p08jqo0%40group.calendar.google.com/public/basic

ICAL https://www.google.com/calendar/ical/pj443o2ojeggqfofr82p08jqo0%40group.calendar.google.com/public/basic.ics

HTML https://www.google.com/calendar/embed?src=pj443o2ojeggqfofr82p08jqo0%40group.calendar.google.com

DEMOs

XML - https://www.google.com/calendar/feeds/irv222fjo1mqo7neukv6dqfuto%40group.calendar.google.com/public/basic

ICAL https://www.google.com/calendar/ical/irv222fjo1mqo7neukv6dqfuto%40group.calendar.google.com/public/basic.ics

HTML https://www.google.com/calendar/embed?src=irv222fjo1mqo7neukv6dqfuto%40group.calendar.google.com

Come Swim with the Dolphins !

An Oracle Open World blog post for us ! It has highlights and links for you to see all the great MySQL content at Open World.

http://blogs.oracle.com/oracleopenworld/entry/come_swim_with_the_dolphins

Oracle Open World MySQL Sessions

Just ten days until Oracle Open World and MySQL is going to have a big presence! 
“Focus on MySQL” overall schedule is available here: http://www.oracle.com/openworld/oow11-focuson-mysql-486114.pdf

Here are a couple thoughts of mine about the week:

Support and visit the MySQL Community Kiosk at Moscone West Hall Level 2 as much as possible.

Sunday @ Moscone West 

A great line-up of presentations by the IOUG/MySQL Community itself. It will be hard to pick which sessions to see. MySQL ACE members Ronald Bradford and Giuseppe Maxia are some examples of the experts talking on Sunday.

Monday @ Marriot Marquis

The MySQL keynote by Tomas Ulin, of course, should not be missed.

Catch sessions by Oracle engineers and MySQL ACE members such as Sherri Cabral, Sarah Novotny, and Yoshinori Matsunobu.

Tuesday @ Marriot Marquis

Looks to have great sessions on MySQL replication, MySQL for Oracle DBAs, MySQL on Windows, as well as InnoDB and the MySQL roadmap. All look to be good to see.

Also take note of the hands on labs and learn from the expert himself.

MySQL Workbench: Developing MySQL Applications on Windows

- Mike Zinner, Software Development Director, Oracle

Tuesday at 11:45am – Marriott Marquis Salon 10/11

The MySQL Community Reception is also open to everyone,  space is limited so register today.

Wednesday @ Marriot Marquis

NoSQL, backup,monitor, data recovery and protection, scalability and Oracle integration talks from the experts. Mat Keep, Andrew Morgan, Rob Young, Lars Thalmann just to name a few.

Thursday @ Marriot Marquis

NoSQL Interfaces to MySQL Cluster, by Craig Russell, and What causes MySQL downtime, by Baron Schwartz a MySQL ACE member,  both look like good sessions. Cloud Computing solutions for MySQL is another good one by Charles Bell.

These are just a few of the 47 MySQL Sessions. All of the sessions are going to be loaded with knowledge from MySQL experts and it will be tough to pick  which expert you want to listen and learn from. 

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/