The world's most popular open source database
Michael "Monty" Widenius is the CTO and co-founder of MySQL AB, and the original developer of much of the MySQL code base. Before Monty went into hiding to dive into work on new features for MySQL 5.0 (and spend time with his newborn daughter), we caught up with him on MySQL 4.1 and the state of the company from his point of view.
MySQL 4.1 is now just about finished. What are the key features in the 4.1 release that users can look forward to?
Monty: The most notable new features are subqueries, prepared statements and Unicode support. It feels very good to finally have these done, as these are things that we have got many requests from our users.
(Editor's note: In the coming weeks, we'll be featuring articles that talk about the new features of MySQL 4.1, including the ones that Monty didn't mention.)
What were some of the tricky parts in implementing subqueries?
Coding subqueries consisted of a couple of different sub-tasks. We had to
change the old query handler code to be re-entrant. That was the main task in
implementing subqueries. We also had to rewrite subqueries to a common form
that is easy to optimize. Internally this means that many subqueries are
EXISTS (SELECT ...) which is then easy to
There are lots of different subquery methods (
SOME etc..) which are not that hard, but still add up to a lot of
work. It was important to optimize some very common, independent subqueries so
that they are as fast as joins. That was one of the hardest tasks and we where
quite happy when we succeeded with that. I think people will really appreciate
As the code base, number of features and size of the team have grown how has your role changed? Do you still review all of the code yourself?
The last two years I've spent more time than usual reviewing code and helping to build up the MySQL organization at the expense of coding MySQL myself. Now that we've hired a professional management team I can concentrate on doing the things I like the most, namely architecture, coding and reviewing code. It's nice to have a lot of knowledgeable new members of the MySQL team to discuss and plan future MySQL development.
I still review all new code that's central to the MySQL server for all active releases. This is still possible as we have other people doing pre-reviews making it more manageable.
What can you tell us about MySQL 5.0? In the past you've been somewhat outspoken about stored procedures. And yet the 5.0 code base contains a number of such traditional database features. What caused the change of heart?
I wouldn't say there's been any real change of heart. I personally prefer to have the application drive the database usage in contrast to having the database do a lot of the application work. It makes the application less database-dependent and ensures that the application is not bound by the limitations of the supported database.
Other database developers have different preferences and needs and I am happy to be able to give them the ability to use stored procedures inside MySQL.
Do you have any recommendations as to when someone should use stored procedures versus other approaches? What are the tradeoffs?
There are a couple of different approaches when it comes to solving complicated tasks that involve databases. You can code the application logic in the application and only use 'simple' commands against the database in a traditional client/server approach. Or you can code a big part of the logic in the database itself.
The main benefits of the first approach, that is putting the logic in the application, are that the application can be more easily made to be database-independent. You're not restricted to database language limitations. SQL in itself is a very limited language and doesn't give a lot of abstraction capabilities. You also get faster code execution of the non-database specific code. There are fewer size limitations as you can use client memory to hold the results. It's also easier to debug with this approach.
The second approach, that is, putting the logic in the database itself, has some different benefits and tradeoffs. You can get faster database object manipulation since you don't have to send data back and forth between the client and the server. For simple applications, it can be easier to code since you've got a central place for all application logic. It's easier to give users privileges to access different application logic. By only allowing users to access data through stored procedures or triggers you can have more control over the data and ensure high data integrity.
How would you compare stored procedures to other approaches like server extensions or user-defined functions?
There are a couple of different ways to put the logic into the database with MySQL. The classic approach is to use stored procedures. These are stored as an array of pre-parsed class objects that are very fast to execute. Stored procedures are useful when you have a complicated task that needs loops, logic and function calls.
Alternatively, you can use User Defined Functions (UDFs). These are coded in C/C++ and can be linked in dynamically when needed. UDFs are useful when you need high speed and the result is only dependent on the arguments to the function.
You can also use server extensions, like the MySQL internal
ANALYSE() function. This will actually be renamed to
CHANNELs in MySQL 5.0. Server extensions are useful when you need
to extend MySQL to pre-process or post-process the retrieved data in some way
that's very hard in SQL. There are some good analyse examples in the manual and
reviewing the analyse source code itself is a good way to see what can be done
with server extensions.
Lastly, another approach would be to add a new table handler to MySQL. This is useful when you need to get access to external data from MySQL or you have very specific needs for your application, such as being able to store data in a compressed format. You can also extend the MySQL server with new commands. But that's something you should only do if the earlier methods can't solve the problem at hand.
What about embedded applications?
This is another approach you can take which is mainly useful for single
user applications or embedded applications. In these cases you can integrate
the application and the database in the same application, for example if you
link the application with the embedded
mysqld library. This is an
important feature we added specifically to make it easier for people to
use MySQL with deeply embedded applications. This integrated
approach has similar tradeoffs and benefits to the traditional client/server
approach, though you get fast database object manipulation since it's directly
linked into the application.
This year you had a very successful user conference and also there's the upcoming MySQL Swell GeekCruise conference in October with you, David Axmark, Brian Aker and some of the other MySQL execs. What made you want to do this event?
I went on a Linux GeekCruise earlier and it was a very rewarding event for me. To be able to mingle and discuss (for days) your Linux problems and ideas with some of the important people behind Linux was both fun and very educational.
We wanted to give MySQL users the same opportunity that the Linux people have on the Linux GeekCruise. People can come discuss and learn about MySQL and also get practical tips and solutions to problems that they face when using MySQL. There is plenty of free time during the MySQL Cruise, and I am sure we will spend a notable part of that time to discuss solving practical problems given to us by users on the trip.
Another reason that I like events like the MySQL GeekCruise is that it gives me the chance to meet MySQL users and through them get a better understanding of what they really want from the MySQL products in the future.
How has the company changed since you raised venture capital? Do you think MySQL can stay true to its open source roots or is there pressure to be more "corporate"?
The venture capital has not changed much, except giving us a larger freedom to employ more people. The main problem we have faced is that there are now more people involved in the day-to-day life in MySQL, which makes it increasingly harder to ensure that everyone knows what the 'MySQL spirit' is all about. For us this is harder than in most companies in that we are a virtual company with employees all over the world.
However, I'm confident that MySQL AB will stay true to the open source ideals and also be able to do a solid business on top of that. By being both open source and commercial we can get the best benefits from both worlds.