Special thanks to all those who came out for the MySQL Workbench Tutorial and Workshop sessions, I hope they were of use to you.

The materials are now online. A PDF of the slides is available here and a video of the slides and speaker is available here. These materials are also listed on my Presentations page.

Please note that the video is RTSP streaming MP4, allowing you to jump around the video at will. You’ll need Quicktime, VLC or another appropriate player to view it. I’m trying something new with the built in camera on my laptop, providing a video image of the speaker along with the slides. Let me know what you …

Whether you come to Monday’s Tutorial or the Workbench Workshops on Tuesday-Thursday I strongly recommend you bring a laptop. We’ll be doing a lot of hands-on in both and you’ll be missing out on a lot of the content if you don’t have a copy of MySQL Workbench in front of you (we’ll supply the copy of Workbench, in fact you get the SE edition free for a year just for attending).

You should also act now to register for a Workshop if you haven’t already as Tuesday is already “sold out”. Admission is free but we only have 40 seats in each daily workshop.

For those choosing between tutorial and workshop, I’ll say upfront that we’ll feature the same content in both the …

Working on server-based index analysis I once again recalled a topic I wanted to write about for many times. The topic is redundant “Using where” in query plans. Suppose we have a table like this:

mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) NOT NULL DEFAULT ‘0′,
`b` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`,`b`)
) ENGINE=PBXT AUTO_INCREMENT=25651 DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

now let’s try to analyse 2 simple queries with EXPLAIN SELECT:

1. SELECT a FROM t1 WHERE a>1;
2. SELECT a FROM t1 WHERE a>1 AND b>1;

mysql> explain select * from t1 where a>1 \G
*************************** 1. row ***********
id: 1
select_type: SIMPLE
table: t1
type: range
…

In an unexpected turn of events I am speaking at the 2009 MySQL Conference after all, filling in as a presenter for a Monday tutorial called Introduction to Data Modeling with MySQL Workbench.

In addition to the Monday workshop, we’ll be delivering a shortened tutorial version each day of the conference. Attendees get a complimentary one year subscription to the SE version of MySQL Workbench. You can register at http://dev.mysql.com/workbench/?page_id=206. Spaces are limited to 40 for each session, as of now space is available but you’ll want to register sooner than later.

What will be be covering? So far the lesson plan is shaping up as follows:

Introduction

- Who Are We?

- Who Are You?

- What is the Session About? …

Finally QOT development has reached an important milestone - all the tests were migrated to the new testing framework. The latest changes can be found at Launchpad - lp:qot. This switches the green light to the development of new features.

The first thing to be implemented is data schema reading from server so it is no more necessary to provide a script with DDL. Basically the code is already written but not yet well tested and the documentation is yet to be updated. Command line options are similar to MySQL (–host, –port, –user, –password). So far only TCP connections are supported, but of course I’m planning to add Unix sockets and Windows pipes. For those who would like to give it an early try I have pushed the code to a separate branch: …

Long saught and recently implemented in HeidiSQL: An editor for stored procedures and functions. See it in action by updating to a beta build:

There is now an alternative to setting up your own PHP environment. You can download a server-only OpenSolaris web stack and run it in
a virtual machine. XDebug, the PHP engine, Apache, and MySQL are preconfigured on the web stack. You can work on your PHP project in NetBeans IDE and use the FTP feature to deploy your PHP on the OpenSolaris web stack.

Follow these steps to use the OpenSolaris web stack:

1. Download the OpenSolaris web stack image and create a virtual machine for it.
2. Configure the network connection on the virtual machine.
3. Configure port forwarding.
4. Create an FTP connection in NetBeans for the web stack on the virtual machine.

See the tutorial Deploying PHP to a Server-only OpenSolaris Web Stack for detailed instructions for setting up the OpenSolaris web stack on Sun …

Python support has been added to the latest version of MySQL Workbench.

In addition to Lua, you can now write scripts and modules or interact with the GRT shell using the Python language. The integration allows you to use GRT objects and modules mostly in the same way you would do with normal Python objects. The built-in grt module contains everything related to the GRT that’s exposed to Python, including:

• custom types for GRT lists, dicts and objects
• wrappers for GRT classes, that can be used and instantiated as a normal Python class;
• wrappers for registered GRT modules, that can be used like normal modules;
• a reference to the root node of the GRT globals tree;

You can inspect these objects with the standard dir() command and in some cases with help(), to access the …

It’s certain now that Peter Gulutzan will be at the MySQL User Conference, live on stage.

Details within a few days.

Last week (or rather last weekend) was quite productive. I was able to make a good progress migrating to the new mysql-test based testing framework. Reviewing the tests I was able to find and fix some bugs. For example there was a bug that ignored some columns while detecting index capabilities. As I mentioned earlier I also started to rework the output format. Here’s an example:

/*

Query:

select sum(col2) from t2 group by col1selectivity:

all rows

used tables:

t2 (all rows)

used aggregate functions:

sum ( col2 )

ordering:

implicit

related existing indexes:

t2.ix123(col1, col2, col3): covering, optimizes ORDER BY, optimizes GROUP BY

*/

As you can see now the tool reports used aggregate functions, if any. This is very convenient for manual query analysis, especially in non-trivial cases.

Next, the “ordering” now can be “implicit”. This is reported for the cases when there’s a GROUP BY clause and no ORDER …