In an earlier blog posting “SQL Standards, ANSI committees, and Sun”, I (Peter Gulutzan) talked about our prospects for joining the American committee charged with database standards, which we typically call “ANSI” although that’s not the formal name (and by the way the formal name is about to change, but I’ll chat about organization some other time).

Well, I’m now Sun’s official voting delegate to the committee. There are also three “alternate” delegates from other parts of Sun; I’ll loosely categorize them as advocates from our PostgreSQL-ophile and Java / Java DB interest groupings. Mostly my concern is the MySQL side of things.

The committee holds frequent meetings by telephone conference, and infrequent ones in personal get-togethers. I’ve just finished attending one of the lengthier meetings. I …

One of most loved feature of SQLyog is the snappy and responsive user interface. Many of our users and customers prefer SQLyog over other tools because of this particular reason.

One reason for the zippy interface could be that SQLyog is entirely developed in C/C++, which tends to be faster than other languages when it comes to raw speed. I don’t want to start a flame war here, but our customers seem to like this fact. Using C/C++ allows us to use the native MySQL C client libraries that gives the best performance as compared to other ways of communicating with MySQL. Using C/C++ also ensures that there are no external dependencies on any bulky frameworks and the download file is relatively small.

Although using a language that complies directly to machine instructions helps a lot, but true speed comes from better algorithms. A similar analogy in the MySQL context would be that you might get some benefits from tuning your …

Last weekend I’ve reached a milestone in my work on the statistical index analysis for QOT. The code is available on Launchpad.

An example is worth a thousand words, so I start with one (once again I’m using employees-db):

$ ./qot –info –host=192.168.200.1 –analyse=possible –schema=employees –query=”select * from dept_emp where (dept_no = ‘d005′ or dept_no = ‘d006′) and (emp_no = 10010 or emp_no = 10011)”

/* Output produced by qot 0.1.6 GPL */
/*
Query: select * from dept_emp where (dept_no = ‘d005′ or dept_no = ‘d006′) and (emp_no = 10010 or emp_no = 10011)

selectivity:
all rows

used tables:
dept_emp (all rows)

ordering:
no ordering

related existing indexes:

…

Assume you have a table with about 300 000 rows, and an indexed column ‘col1′ with only 9 distinct values. Now you got a query like ’select * from t1 where col1 = const’. The questions are

- when the index is faster to full table scan and vice versa?
- does MySQL use the optimal plan by default?

These questions became very relevant now that QOT got server access and is able to gather various table metrics including selectivity. Besides index selectivity the threshold value obviously depends on the storage engine used, so for me it is also interesting to see how our PBXT engine compares to others in this aspect. Namely to InnoDB - an engine with similar transactional properties and MyISAM - a very fast engine for read-only scenarios.

For the test I took the …

jHeidi alpha 4r2 has been released to support the open source database Drizzle. Just use the slider on the connection dialog and the Drizzle profile will appear. Support extends to almost all of jHeidi's MySQL functionality, except for some import/export functionality and there is no user manager due to the plugin model that Drizzle uses.

Note that the JDBC driver for Drizzle is beta but is nonetheless pretty stable.

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: …