MySQL Workbench – A Superficial Review
Those of you who know me, know I am an old school database guy. One of the things I miss from my pre-web programming days is sitting down with an Entity-Relationship Diagramming tool and painting a picture of the database I want for my application. Since moving to MySQL as my primary database, I’ve tried out several ERDs. I’ve loved at least one, hated most others for either lack of features or price tag – or both – or been generally just unimpressed. Recently I got an email from MySQL saying the latest version of their ERD tool had been released. Honestly, it had been a while since I had taken a look at it so I thought maybe I’d give it a go. Below is what I consider a very superficial review of this tool and a couple of conclusions, both good and bad.
I’m running this on my development machine. A Dell Latitude E6400 with a 2.53GHz processor and 3.45 GB of RAM. Currently I have 18 programs open including email, browsers, audio editing and my IDE. This is about normal for me. I didn’t stack the deck for or against Workbench, I was shooting for real world.
The test database if for a sample application I’m building, a ToDo list; three tables and a single view. Not terribly exciting but shows off some most of the standard features of the product.
If you have ever used an ERD tool before, you will be right at home with MySQL Workbench. it’s got all of the features you expect and the overall quality of the program is good. As you can see from this opening screen, you have the options for buildings multiple diagrams for a given model, easy access to the complete tables, routines, users, roles, etc. Honestly, I don’t spend much time on the Model screen since I’m usually working on the diagram itself. The exception to this was when I was adding a user and a role to my system. You can see it in the lower part of the picture if you click on it. Adding a user was a bit confusing at first until I realized that you have to add a role first. Also, I discovered that it’s a bad idea to add a user AND a role named user, it just confusing. :)
The main diagram screen is the canvas you paint your database on. You can see in this picture, I have 3 tables and a view (the yellow thing in the upper right corner of the main canvas) The program uses standard graphics, conventions and notations for diagramming databases. Adding and editing tables works just like you would expect. Click the new table icon, drag it onto the main canvas and drop it. Adding a relationship seemed a bit backwards to me because you click on the child first and then the parent. even so, once you get around this small confusion, the program correctly build the relationship. One really nice touch is that when you mouse over the line for a relationship, it highlights the line and the related fiends with a pastel glow. That makes finding the related fields easy in larger tables.
Unlike other programs of this type that I have used, editing of the properties of an object is not done in a popup window but in a pane at the bottom of the screen. Shown here, the edit pane gives you a tabbed interface across the bottom to edit the different properties of the highlighted object. Tabs across the top of the properties pane give you access to multiple objects that you have clicked on. it’s a bit of a different paradigm but one that, once you get used to, works out pretty well. The only real issue I had was that any context switching seemed a bit slow. If I double click on any given table, if the edit pane was not visible, it could take three to five seconds for it to appear. if it did exist, it could take another three to five seconds to populate it with the properties of the selected object. That aside though, it worked just fine.
One thing I really liked was the view edit screen. Adding a view to the canvas brought up the edit pane and allowed you to type in the SQL to build the view. In the upper right corner of the vide edit pane is a small button showing you whether the code, as currently typed, is valid. it’s a nice touch to make sure you have all your commas in the right place.
As I found out, it doesn’t actually validate the code, it just tells you if it’s syntactically valid. I’m sure validating the code itself would have been much more difficult but it would be a very nice feature for building views. Also, graphically, it would be nice if the view object on the canvas listed the fields from the query. Since the program is obviously parsing the create view, it should easily be able to pull out the file list.
Of course, painting the picture of a database is no good unless you can use it to actually maintain the database. MySQL Workbench does a good job of that. Since this is a superficial review, I’ll only deal with Forward Engineering but it does Reverse Engineering as well as syncing an existing model to the database server.
What you see to the left is the fourth screen in the six screen forward Engineer wizard. This screen shows me the SQL DDL necessary to create my database and give me 3 options. I can copy it to the clipboard to paste into my database too like SQLYog (another awesome database tool), I can save it to a file and use the MySQL cli to create the database or I can continue on – assuming I’ve setup a database connection for the program – and allow MySQL Workbench to create the database for me. Creating the database connection is trivial so I did that and allowed it to create the database. The good news is when it works correctly, it is beautiful. The bad news is, if you have messed up one of your views, or you don’t have the proper rights to create users, you just going to get errors. That gets frustrating.
Obviously, I had errors or I wouldn’t have mentioned it to you. However, once I resolved the errors, everything worked exactly like you would expect it to.
First, this program is slow. I mean seriously slow. Like it was written in Java and the cross compiled into Visual Basic slow. Even when I boot my machine fresh, bring up only MySQL Workbench and work with my sample database, it is not snappy. Three to five seconds might not sound like a lot of time to bring up the edit pane but if you’ve got a large model and are constantly switching around editing different things, it will take years off of your life. Maybe it’s better under Linux or OSX , I didn’t test those but Windows XP it’s like sucking molasses through a straw.
Second, when a program tells me it has validated my SQL DDL, I expect that means that it’s correct. If I’ve misspelled a field name in my view script, I expect it to catch that and tell me my script is invalid. Maybe it can’t do this during a live edit but before you can forward engineer a database you have to click on “Validate” on the first page of the wizard so that it can validate your entire model. At that point, some extra checks to keep me from shooting myself in the foot. Again, on a small model, this is not a huge issue, however, on large models that take serious time to forward engineer, it could seriously impact your FreeCell game.
Is MySQL Workbench perfect? No, but it is good enough. The Community Edition (what I tested) gives you all the features you will need. I’m not sure what other advanced features that the non-free version has (if there is still a non-free version) but I couldn’t find anything that I need that it wouldn’t do out of the box.
This version is much more stable than previous versions and everything I needed it to do, it eventually did. I am very pleased with this project, especially for free. It’s now one of my standard development tools.
I recommend this tool to anyone doing serious database work with MySQL.
Until next time,