Seeing my recent superficial review of MySQL Workbench and knowing that I like SQLyog, my buddies over at webyog pinged me and asked if I would be interested in reviewing SQLyog also. Knowing me as they do (I’ve been a beta tester for several years) they did the one thing they knew would get my attention, they gave me an enterprise license. (Actually, they replaced the one I had and had lost)
To be honest, I could do this review in my sleep. SQLYog is one of the 3-4 programs that is almost always open on my desktop when I am doing development. I’ve been using it since 2003/2004 and for raw manipulation of mysql databases, it just can’t be beat.
At least I thought I could do the review in my sleep until I started playing with it again. Since I last used it they’ve added a bunch of new features. Some of them cool but some of them not really interesting to me personally. Overall though, SQLYog is still the tool that every developer working with MySQL has to have.
Some of the features I will be describing are only available in the Enterprise version of the tool. While they are nice to have, it’s not a deal killer if they aren’t available because I’m using the COmmunity Version. SQLYog, unlike a lot of companies, has managed to remain true to the “open core” concept of making a very useful free version of their tool and then adding features to the commercial version that make life easier but aren’t necessarily “must haves”.
The Query Window
This is the window I have lived in for the past few years when creating, debugging or maintaining MySQL databases. This is where the action really happens. The upper right window is where you enter your query. Then you press F9 (or click the cute little Play button) and execute the query. If it’s a select statement (as the example is) you will see the results in the window below it.
This view gives you more than just the ability to execute a query though. The window beneath the Query window has 6 tabs that each present you with different views of either the query you just executed or the table selected to the left in the database browser. The tabs are:
- Result – the result set of the last query executed
- Profiler – An indepth look at the query you just executed including the EXPLAIN for it. This is so much more than EXPLAIN though. It gives a complete breakdown on the steps it took to execute the query. This is new since the last time I played used SQLYog but I can tell that this tab and I are going to be good friends.
- Messages – Any messages that would normally be returned by MySQL. For example, the number of rows affected, etc.
- Table Data – This shows the currently selected table on the left in a browsable, and editable, grid. Back in my FoxPro days we used to call this a “Danger browse” because it will allow you to edit the raw database.
- Info – Information about the currently selected table including the DDL necessary to create it.
- History – A complete running history of all commands executed in this query window. Very handing for scrolling back to find out where you made your last mistake.
Here are thumbnails of the different tabs described above. Click on them to see a bigger view.
The Query Designer
Ok, at first blush, this feature is a bit reminiscent of “Access” and if you look at it and have flash backs, don’t worry they pass. The resemblance however, is only superficial. The difference between this query designer and the one in the last version of Access I used (’97?) is that this one works. In the upper window, you drag tables over from the database selected on the left. It will automatically figure out relationships for you.
Then below, you can selected tables and fields that are represented above. If you don’t enjoy writing SQL by hand or if you have a particularly nasty query you need to think through, the Query Designer is a good tool for you. it shows you the SQL for the query you are building and there is even a button to create a view from the query.
One small gripe, if you are not creating a view then the only way to use the code you have created is to copy it and past it into the query window and execute it.
If you read my recent my recent superficial review of MySQL Workbench then you know I love data modeling. I think in databases when designing new systems, so ERD tools are important to me. I remember a few years ago when the nice people at SQLYog told me they were going to include an ERD tool in SQLYog, I told them I wish they would put their efforts in other areas. There were already good tools available to do ERD and it just seemed a shame to tack that onto SQLYog. After seeing the finished product, I wish they had heeded my advice.
The Schema Designer included in SQLYog is a toy compared to MySQL Workbench. It would be a decent visualization tool, if it were a bit easier to use but since I have to drag each table over individually to add it to the schema, it’s cumbersome for all but the most simple of databases. Also, instead of sticking to accepted standard notation to denote relationships like MySQL did, SQLyog uses a custom notation that, while not confusing, is not necessary.
While the Schema Designer is easy to ignore in SQLYog and therefore does not count against it, it would have been nice to make this a separate tool so that those of us using better tools didn’t have to waste the resources it takes up every time SQLYog is loaded.
It’s the little things
The think I like about SQLYog the most are the little things. Things like the User Manager and the fact that if your MySQL database runs attached only to
127.0.0.1 they provide a little PHP adapter that you can drop on your server to allow you to still use it instead of having to resort to inferior tools. the user Manager, while still a bit cumbersome, even after all these years, is still better than issuing
GRANT statements by hand.
Also (and this is my favorite feature because I suggested it to them way back when I was an active beta tester and they implemented it) They have a great Schema Synchronization Tool. So when you get ready to migrate your staging database into production, calling up both in the Schema Synchronization Tool will show you the differences, allow you to mark the changes you want made and then it generates the DDL necessary to make them. It truely is a powerful part of this great tool.
There are always things I don’t like about every program. I don’t like that a couple of years ago, I upgraded my copy of SQLYog only to find out that “Execute Query” had shifted from F5 to F9. (I hate it when things like that change) As stated, I don’t particularly like the Schema Designer. As for as much as I use it, the user Manager is still very clunky. Compared to the rest of the program, where I’ve watched constant refinements in the interface, the User manager still feels like it’s stuck in 1999. Also, it would be nice if the new Query Designer allowed you to execute the query in place instead of copying it to the query window and executing it there. if you can’t figure any other way to do it guys, when I press F9 in the query designer, open a new query window for me, paste the SQL into it and execute it. it’s not an elegant solution but it would work.
I LOVE this program. I’ve used it for years and will use it for years to come. Yes, there are other programs on the market that do the same thing but this is the one I like so it’s the one I use.
SQLYog comes in two version. The Community Edition which is free and the Enterprise Edition which costs $118 with a year of support. Here is the complete list of features and what is included in each version.
If you have a favorite tool for working on databases, great, keep using it. if you don’t have one though, or if you are unhappy with your current tool, download the Community Edition of SQLYog and take it for a spin.
Until next time,