contents/articles/sqlyog5-review.html
1 2 3 4 5 6 7 |
----- title: SQLyog 5 - a fast and reliable MySQL front-end content-type: article timestamp: 1141131000 tags: databases|review ----- MySQL[1] is a great database solution. Literally millions of people who use it can tell you that it is a well-performing, feature-rich database solution for almost any size project: it is low-cost (often free), and available on the majority of webservers all over the world. When I first discovered MySQL while learning some basic PHP programming, I almost immediately wondered how I'd effectively access MySQL and manage my databases other than through PHP code or command line. I was pointed to PHPMyAdmin[2], which I still use as a quick, general-purpose MySQL front-end. However, I wondered if there was anything better than that, and maybe not confined within a browser window...<br /><br />There are a few desktop "cousins" of PHPMyAdmin out there, especially for Windows, which is not surprising. After a quick search, three products come up immediately: MySQL-Front[3], Navicat MySQL[4] and SQLyog[5], all of them are proprietary solutions and seem to be the most popular ones around. <br /><br /><em>Alright, which one is the best?</em><br /><br />There are many different criteria available to choose a winner among these three products. The easiest for me was simply: "which one is free?"<br /><br />- SQLyog, with some restrictions, is our instant winner. Both MySQL-Front and Navicat MySQL offer a 30-day trial, while SQLyog can be free for life but only with basic features. However, the number of basic features is considerable.<br /><br /><strong>First impressions</strong><br />After launching SQLyog (free edition), a small and not-too-annoying nag screen appears: you click on it and it goes away, it doesn't last for 10 seconds like some others. The same screen appears when you try to access the power tools and advanced features which are not included in the free edition. I got used to it after a short while, and that's the only annoyance of the free version of the product. <br /><br />The program's interface seems a bit unconventional for the traditional Windows user, especially if compared to the other two products. The main window is divided into four parts: the main menu and a navigation bar underneath it, a left column listing all the databases and tables in an expandable tree, the top half of the main window which hosts a SQL editor, and the lower half with everything else, including a tabbed area for displaying query results, messages, table data, table structure and history.<br /><br />It seems as if the SQL editor should be in a tab as well, but after using SQLYog for a while, you understand why is not: the editor has been positioned such that it can be used often, easily, and immediately. It took me a while to figure this out, but once you embrace this philosophy, you'll never stop using this program; all front-ends include a query editor, but it's often relatively hidden, meaning that it is at least one or two clicks away from the rest of the interface.<br /><br /><br /><strong>Main Features</strong><br />After specifying your credentials, the program will connect to the MySQL server and list all of the available databases in an Explorer-like left side panel. All tables can be accessed by clicking once on the corresponding database. All column fields, indexes and triggers (if any) are displayed by clicking on each table name.<br /><br /><em>So when I click on a database or a table the corresponding structure is displayed, right?</em><br /><br />Wrong. When you do that, nothing happens. Remember the multi-tabbed lower panel, which is supposed to display results, table data, objects, etc.? Well, the focus is set to the <em>Result</em> column by default, so if you want to display the database or table structure you need to click on the <em>Objects</em> table, and voil� , the structure appears. Fortunately this behaviour can be changed by modifying the program's options, through the Tools menu.<br /> <br />Clicking on <em>Table data</em> will display the first 50 records of the selected table, while the <em>Result</em> and <em>Messages</em> tabs will still be empty; the editor wasn't used, so there's no result to show, and we didn't get any errors or other messages from MySQL yet, so everything is as it should be.<br /><br />The most interesting feature from an educational point of view, so far, is actually the <em>History</em> tab, which is just one click away and shows the following:<br /><br /><code><br />/*[11:11:11 AM][ 0 ms]*/ show variables like '%character%'<br />/*[11:11:11 AM][ 0 ms]*/ Set character_set_connection=latin1<br />/*[11:11:11 AM][ 0 ms]*/ Set character_set_results=latin1<br />/*[11:11:11 AM][ 0 ms]*/ Set character_set_client=latin1<br />/*[11:11:11 AM][ 0 ms]*/ set sql_mode=''<br />/*[11:11:11 AM][ 15 ms]*/ show databases<br />/*[11:11:22 AM][ 0 ms]*/ use `zzine_drupal`<br />/*[11:11:23 AM][ 203 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` <br /> where `TABLE_SCHEMA` = 'zzine_drupal' and `TABLE_TYPE` = 'BASE TABLE'<br />/*[11:11:32 AM][ 47 ms]*/ show full fields from `zzine_drupal`.`node`<br />/*[11:11:32 AM][ 140 ms]*/ show keys from `zzine_drupal`.`node`<br />/*[11:11:32 AM][ 0 ms]*/ select * from `zzine_drupal`.`node` limit 0, 50<br /></code><br /><br />The above is a log of all the SQL commands which were sent to the server so far: the program connected and showed all the databases, I clicked on the "zzine_drupal" database, got some info about it, and then clicked on the <em>node</em> table and displayed the first 50 records. So, if you are new to SQL and want to learn the syntax to query the database you can just have a glance at this tab every so often.<br /><br />Note that the time to execute a query is displayed in ms, and it's not wrong! SQLyog actually performs quite well, as boasted on the official site's features page[6]: <em>"[it] uses native MySQL C API - the fastest way to communicate with MySQL server"</em> - and they do mean it.<br /><br /><em>What else does SQLYog offer?</em><br /><br /> The free version includes the most used features, like the very two most basic operations: you can query the database by typing an SQL query into the editor and executing it (F5 or F8 if you want to edit the results) and change the value of each field through a convenient blob editor, which can display text or images, import content from a file or save it locally.<br />Then the program groups all functionalities in standard dropdown menus on the top bar, and also presents the most used operations as clickable icons as well. Now, this can be handy, but the program displays 25+ icons without any text underneath, so either you keep hovering your mouse on each one waiting for an explanation message to appear, or you just use the standard dropdown menus anyway. The authors did an outstanding job creating an icon for (literally) every action: they are quite well made and explanatory enough if you look at them carefully, but they are still very similar, and too numerous to memorize.<br /><br />However, SQLyog is also 100% keyboard friendly, as almost every function has a shortcut. Memorizing just a few of them, and it's worthwhile, as it makes everything much faster. At any rate it's better than memorizing all the icons instead!<br /><br />Let's examine each dropdown menu and the functions listed in them.<br /><br /><span style="text-decoration:underline;">File</span><br />This menu lists all the functions concerning database connection and disconnection, opening and saving SQL files, and opening new query tabs - the SQL editor panel can have multiple tabs.<br /><br /><span style="text-decoration:underline;">Edit</span><br />This menu refers to the SQL editor, not to the query results! It includes functions like execute queries, copy, paste, cut, undo, redo and find/replace, which does not find strings in a record/table/database, but only in the SQL editor. I do believe the "Find in Database" function is missing in SQLyog, and is present in some of the competitors, but you can search your database using the appropriate SQL queries, right? Maybe - in my opinion - an advanced <em>Find <something> in <somewhere></em> wizard or dialogue should be implemented - as the developers seem to be very good at creating those types of things, we'll soon find out.<br /><br /><span style="text-decoration:underline;">DB</span><br />Maybe I'd have called this "database" for the sake of newbies, but this menu indeed groups all database-related functionalities together. Create/truncate/drop databases, create table and create view (maybe they could have been placed under the <em>table</em> menu), and other interesting features like creating an HTML schema on the database and even copying a whole database (or just a few tables) to a different host (even remote, if accessible) with a single click! It works, just don't try to copy a database onto another remote server on a 56K dialup connection, like I did...<br /><br /><br /><span style="text-decoration:underline;">Tables</span><br />Another self-explanatory menu, listing all table-related operations like create, alter, rename, empty, drop, import, export tables, manage indexes, rearrange columns, etc. All these functions can be performed through wizards, dialogues or other equally simple methods that any average Windows user should be familiar with. There are only a few exception here and in other menus: when wizards would be inappropriate or inadequate for certain actions, SQLyog prepares a "template query" and lets the user fill it in, typically for more advanced needs, such as if you want to create a new (MySQL 5.0+ only) <em>trigger</em> named <em>test</em> on the <em>node</em> table of the aforementioned zzine_drupal database. For this, SQLyog prepares the following query template: <br /><br /><code><br />DELIMITER $$;<br /><br />DROP TRIGGER `zzine_drupal`.`test`$$<br /><br />CREATE TRIGGER `zzine_drupal`.`test` BEFORE/AFTER INSERT/UPDATE/DELETE on `zzine_drupal`.`node`<br />FOR EACH ROW BEGIN<br /><br />END$$<br /><br />DELIMITER ;$$<br /></code> <br /><br />...just remember to modify it according to your needs!<br />Do you like SQLyog's query templates? Check out Edit->Insert Templates and there's almost everything for every taste.<br /><br /><br /><span style="text-decoration:underline;">Objects</span><br />Presumably the authors created this menu to group some advanced or new functionalities together, but everything listed here is already present in one of the other menus: management of functions and triggers, view-related actions, and stored procedures... except for the <em>Drop Column</em> action, which is only available under this menu.<br /><br /><span style="text-decoration:underline;">Tools</span><br />This menu also lists two actions which we already saw under the <em>DB</em> menu, which is exporting or importing a database. however, there is also an <em>Export resultset</em> wizard, as well as a very handy user management tool, information about the current database, and the program preferences.<br /><br />The program preferences apparently have two settings which perhaps should be changed by default, which concern the previously mentioned weird tab focus: if you'd like something more intuitive and you don't need (or want) to use the SQL editor a lot, you can safely unclick the "Keep focus on SQL Editor after query execution" and click the "Always select Objects tab when a new item is selected".<br /><br />Believe it or not, you get all this for free. No charge, no trial periods: these are the actual features offered by the free edition of SQLyog! No surprise that over 500,000 people already downloaded it!<br /><br />What's in the <em>Professional</em> and in the <em>Enterprise</em> edition then? Nothing much, and <em>Power tools</em>.<br /><br /><br /><strong>"Power Tools"</strong><br />When I wrote <em>nothing much</em> earlier I actually referred to the Professional Edition, which - as the feature matrix shows[8]- doesn't offer anything more than the free edition: basically you pay $9 to get rid of the nag screens, which are normally not very intrusive...<br /><br /><span style="text-decoration:underline;">Tunneling</span><br />On the contrary, the Enterprise Edition ($49) has a lot of very interesting advanced tools which are actually worthwhile to have. Perhaps the most essential feature missing in the free version, especially for people using a remote hosting solution, is <em>tunnelling</em>. You can use SQLyog to connect to a remote server, theoretically; in reality though, in order to do so your hosting provider must allow privileged remote connections to the database (i.e. <user>@% instead of <user>@localhost), which is not permitted 98% of the time for security reasons. So how can you use SQLyog to access your remote database(s)? With tunneling.<br />The concept is simple: even if privileged remote connections are normally not permitted, privileged local connections are. So all you need to do is place a PHP script on your server, somewhere accessible, and specify it as a parameter for HTTP tunneling before establishing the connection; SQLyog will then access the script and the script will basically forward SQLyog's instruction to the database server, just as if the commands were issued locally. <br /><br /><em>I will never allow commands to be sent to my server unencrypted and through a PHP script, which can be exploited by the first script-kiddie passing by!</em><br /><br />This is a common, slightly biased, but ultimately reasonable concern, and for $49 you can also get SSH tunneling, provided that your host allows you to connect to the server through a SSH shell. I tried this option and it worked perfectly: with a 2MB/s ADSL connection all went smoothly and fast: the program proved to be a valid alternative to PHPMyAdmin in terms of speed and responsiveness. <br />Do not try this on a 56K connection! It's not worthwhile, and probably not even conceived of by the developers. As I always want to try extreme solutions, I also tried SSH tunneling on dialup and my final conclusion was: <em>stick with PHPMyAdmin</em>. SQLyog seems to have been developed in order to achieve relatively immediate responses, as a result, when a low speed connection is used to connect to a remote database, the program may hang for a little while before delivering results and executing queries as normal. Perhaps there's room for improvement here: it would be great to have progress bars display when an operation takes more time than normal.<br /><br /><span style="text-decoration:underline;">Database Synchronization and Migration</span><br />A common and useful feature you should expect from a MySQL front-end is a synchronization utility, and SQLyog has one: by clicking on <em>Database Synchronization</em> under the Powertools menu you can start a quick and easy synchronization wizard, to automatically update two databases. Simply provide the connection details (even if they are on different hosts or require tunneling), and select the databases you want to synchronize, also specifying if you want a two-way synchronization or only one way. A similar function is <em>structure synchronization</em>, which can be used to keep only the structure (not the data) up-to-date between two databases. You won't be asked to create two new connections, but the operation can only be performed on databases that are already accessed by SQLyog.<br /><br />For more information on how to take advantage of SQLyog's advanced synchronization features, I recommend reading a very informative article specifically devoted to this subject, available online[8].<br /><br />Another VERY interesting features SQLyog offers (which has been the subject of a whole article on DatabaseJournal.com[9]) is the possibility to easily migrate to MySQL from other ODBC sources. Through a relatively painless wizard it is possible to migrate from another database type to MySQL, while making sure that any errors are handled as expected.<br /><br /><span style="text-decoration:underline;">Periodic Tasks and Management</span> <br />MySQL is a wonderful relational database, but it fundamentally lacks the ability to execute scheduled queries and operations, which are normally accomplished by server-side scripts. SQLyog offers you the opportunity to easily create and administer periodic tasks, notifications and backups via a few wizards: the <em>Notification Services</em> wizard, which can be used to send the result of a particular user-defined periodic query to an email address or execute maintenance queries, and the <em>Scheduled Backups</em> wizard to automate full or partial database backups and exports. Webyog[10] itself offers an informative how-to[10] on these tasks, step-by-step with screenshots. Last but not least, you can manage all these scheduled jobs through a very handy <em>job manager</em> located in the <em>Powertools</em> menu.<br /><br /><br /><strong>Final Judgement</strong><br />SQLyog is a well-rounded, multi-functional front-end for MySQL which can be used by both newbies and more experienced users to manage their databases. I'd clean up and reorganize the interface a little bit and remove a lot of the icons as well as list all the functions under the top menus, possibly <em>without</em> the icons and without repeating the same function anywhere.<br /><br />Apart from those small items, SQLyog is definitely worth a shot, and the Webyog team definitely did a good job in this fifth version by incorporating all the latest MySQL 5 functionalities in an already excellent program. The free version in particular offers quite a wide range of functionalities with no trial period, and this certainly helped the program to grow in popularity. I would never buy the Professional edition, simply because it only gets rid of nag screens without offering nothing new over and above the Free Edition. On the other hand, the Enterprise Edition is an excellent and inexpensive solution if you need the power tools.<br /><br />SQLyog is just a few clicks away[11], only 7 Megabytes, and ready to install! <br /><br /><br /><strong>Notes</strong><br /><small>[1]MySQL - Official Site: <a href="http://www.mysql.com/">http://www.mysql.com/</a> <br />[2]PHPMyAdmin - Official Site: <a href="http://www.phpmyadmin.net/home_page/index.php">http://www.phpmyadmin.net/home_page/index.php</a><br />[3]MySQL-Front: <a href="http://www.mysqlfront.de/">http://www.mysqlfront.de/</a><br />[4]Navicat MySQL: <a href="http://www.navicat.com/">http://www.navicat.com/</a><br />[5]Webyog Website: <a href="http://www.webyog.com/">http://www.webyog.com/</a><br />[6]SQLyog, feature page: <a href="http://www.webyog.com/sqlyog/index.php ">http://www.webyog.com/sqlyog/index.php </a><br />[7]SQLyog, features matrix: <a href="http://www.webyog.com/sqlyog/featurematrix.html">http://www.webyog.com/sqlyog/featurematrix.html</a><br />[8] Peter Laursen & Quy Ton, "Using SQLyog Enterprise to Effectively Synchronize MySQL Databases" (PDF):<br /><a href="http://www.webyog.com/articles/Using_SQLyog_Enterprise_to_Effectively_Synchronize_MySQL_Databases.pdf">http://www.webyog.com/articles/Using_SQLyog_Enterprise_to_Effectively_Synchronize_MySQL_Databases.pdf</a><br />[9] Peter Laursen, "Migration to MySQL with SQLyog ver 4.1" : <a href="http://www.databasejournal.com/features/mysql/article.php/10897_3550146">http://www.databasejournal.com/features/mysql/article.php/10897_3550146</a><br />[10]Webyog, "How to use Scheduled Backups with SQLyog": <a href="http://www.webyog.com/articles/how_to_use_scheduled_backup.html">http://www.webyog.com/articles/how_to_use_scheduled_backup.html</a><br /></small><br /> |