-----
title: "SQLyog 5 - a fast and reliable MySQL front-end"
content-type: article
timestamp: 1141131000
tags: "databases|review"
-----

<p>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...</p>
<p>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.</p>
<h4>Alright, which one is the best?</h4>
<p>There are many different criteria available to choose a winner among these three products. The easiest for me was
    simply: "which one is free?"</p>
<ul>
    <li>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.</li>
</ul>
<h3>First impressions</h3>
<p>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.</p>
<p>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.</p>
<p>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.</p>


<h3>Main Features</h3>
<p>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.</p>
<h4>So when I click on a database or a table the corresponding structure is displayed, right?</h4>
<p>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 behavior can be changed by modifying the program's options, through the Tools menu.</p>
<p>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.</p>
<p>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:</p>
<pre><code class="sql">
    /*[11:11:11 AM][   0 ms]*/ show variables like '%character%'
    /*[11:11:11 AM][   0 ms]*/ Set character_set_connection=latin1
    /*[11:11:11 AM][   0 ms]*/ Set character_set_results=latin1
    /*[11:11:11 AM][   0 ms]*/ Set character_set_client=latin1
    /*[11:11:11 AM][   0 ms]*/ set sql_mode=''
    /*[11:11:11 AM][  15 ms]*/ show databases
    /*[11:11:22 AM][   0 ms]*/ use `zzine_drupal`
    /*[11:11:23 AM][ 203 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES`
                               where `TABLE_SCHEMA` = 'zzine_drupal' and `TABLE_TYPE` = 'BASE TABLE'
    /*[11:11:32 AM][  47 ms]*/ show full fields from `zzine_drupal`.`node`
    /*[11:11:32 AM][ 140 ms]*/ show keys from `zzine_drupal`.`node`
    /*[11:11:32 AM][   0 ms]*/ select * from `zzine_drupal`.`node` limit 0, 50
    </code></pre>
<p>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.
</p>
<p>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.</p>
<h4>What else does SQLYog offer?</h4>
<p>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.</p>
<p>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.</p>
<p>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!</p>
<p>Let's examine each dropdown menu and the functions listed in them.</p>



<h4>File</h4>
<p>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.</p>
<h4>Edit</h4>
<p>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.</p>
<h4>DB</h4>
<p>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...</p>
<h4>Tables</h4>
<p>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 exceptions 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:</p>
<pre><code class="sql">
    DELIMITER $$;
    
    DROP TRIGGER `zzine_drupal`.`test`$$
    
    CREATE TRIGGER `zzine_drupal`.`test` BEFORE/AFTER INSERT/UPDATE/DELETE on `zzine_drupal`.`node`
    FOR EACH ROW BEGIN
    
    END$$
    
    DELIMITER ;$$
    </code></pre>
<p>...just remember to modify it according to your needs!<br />Do you like SQLyog's query templates? Check out
    Edit-&gt;Insert Templates and there's almost everything for every taste.</p>



<h4>Objects</h4>
<p>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.</p>
<h4>Tools</h4>
<p>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.</p>
<p>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".</p>
<p>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!</p>
<p>What's in the <em>Professional</em> and in the <em>Enterprise</em> edition then? Nothing much, and <em>Power
        tools</em>.</p>



<h3>"Power Tools"</h3>
<p>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...</p>
<h4>Tunneling</h4>
<p>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.</p>
<p>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.</p>
<p><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></p>
<p>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.</p>
<p>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.</p>


<h4>Database Synchronization and Migration</h4>
<p>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.</p>
<p>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].</p>
<p>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.</p>
<h4>Periodic Tasks and Management</h4>
<p>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.</p>

<h3>Final Judgement</h3>
<p>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.</p>
<p>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.</p>
<p>SQLyog is just a few clicks away[11], only 7 Megabytes, and ready to install!</p>
<h3>Notes</h3>
<small>
    <ul>
        <li>[1] MySQL - Official Site: <a href="http://www.mysql.com/">http://www.mysql.com/</a></li>
        <li>[2] PHPMyAdmin - Official Site: <a
                href="http://www.phpmyadmin.net/home_page/index.php">http://www.phpmyadmin.net/home_page/index.php</a>
        </li>
        <li>[3] MySQL-Front: <a href="http://www.mysqlfront.de/">http://www.mysqlfront.de/</a></li>
        <li>[4] Navicat MySQL: <a href="http://www.navicat.com/">http://www.navicat.com/</a></li>
        <li>[5] Webyog Website: <a href="http://www.webyog.com/">http://www.webyog.com/</a></li>
        <li>[6] SQLyog, feature page: <a
                href="http://www.webyog.com/sqlyog/index.php ">http://www.webyog.com/sqlyog/index.php</a></li>
        <li>[7] SQLyog, features matrix: <a
                href="http://www.webyog.com/sqlyog/featurematrix.html">http://www.webyog.com/sqlyog/featurematrix.html</a>
        </li>
        <li>[8] Peter Laursen & Quy Ton, "Using SQLyog Enterprise to Effectively Synchronize MySQL Databases" (PDF): <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>
        </li>
        <li>[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>
        </li>
        <li>[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>
        </li>
    </ul>
</small>