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 …

 With this post I want to start the practice of writing short weekly summaries of QOT activities.

This week I was able to work on some bug reports: #12 #13 #14 #15. Some trivial crashes, some of which were already fixed before. As I wrote in my previous post, I have changed the way I do the releases so if you want to have all the latest features and fixes be sure to use the latest Launchpad version of QOT.

Besides the bugs I started to rework my testing environment. Earlier I had a set of C++ - based unit-tests. I used while-box …

Sorry for the downtime of this site - until around a week ago I hosted my home page on a trusty Genesi Pegasos II system (powered by a PowerPC G4 Processor clocked at 1GHz, using Debian 4.0 PPC with 512 MB of RAM), serving these pages from my home DSL connection. Unfortunately this system provided no means of redundancy - the hard disk drive died.

Luckily I perform frequent backups, so I moved most parts of the site to a shared hosting space now - the picture gallery is unfortunately too big to fit into the space that I have there. I'll try to move the pictures into my Flickr account instead, but this will take some time.

Note that the primary domain name of this site is now lenzg.net - lenzg.org, (the …