SQLyog is a professional-grade GUI tool for the MySQL RDBMS. SQLyog is available for the Windows operating system starting from Windows XP/Windows 2003 to Windows 8/Server 2008 R2. It will also work under Linux, Unix and Mac OS X using the Wine environment. Moreover, a subset of SQLyog Enterprise/Ultimate functionalities are available with the free SQLyog Job Agent (SJA) for Linux as a native Linux utility. This makes it possible to specify and test ‘scheduled jobs’ on a Windows environment and port execution parameters seamlessly to a Linux environment.
Why Use a Database Administration Tool
Database administration tools provide a GUI/Web interface to automate database tasks like querying tables, find and replace operations, and any other task that you might want to run on a database. With respect to MySQL, it offers the free MySQL Workbench. Although it’s far more visually appealing than the command line interface, performance is less than stellar when under a high workload. As such, many users have reported that MySQL Workbench hangs or fails to respond. Frustration has led DBAs to the far superior paid solutions such as SQLyog. It not only delivers more robust operation, but also comes with a variety of useful features that help you get the most out of your DBA tasks.
Throughout the remainder of this blog, we’ll explore how SQLyog helps boost your productivity via some of its unique and value-adding features.
Session Restore
Have you ever experienced the frustration of your system crashing, or accidentally closing your database administration application and losing all your unsaved tabs and queries? If you’re using SQLyog, you can breathe a sigh of relief; we’ve got you covered!
With the Session Restore feature, restore your previous session along with all the query tabs, query builder tabs, schema designer tabs and the history tab just the way they were prior to the crash.
In fact, SQLyog will always restore your previous session on startup by default. However, you can turn off this behavior from the Others tab of the main Preferences dialog. It’s accessible by selecting Tools > Preferences from the main menu.
Session Savepoints
SQLyog also has the ability to save Session Savepoints and open connections from a save point to the same state as when the save point was generated.
As in the automatic restoring of the previous session on startup, the term “session” encompasses all open connections as well as all Query, Query Builder, Schema Designer tabs opened for every connection as well as the History tab.
The Session Savepoint commands are located on the File menu.
To save the current session, select either Save Session… or Save Session As… from the File menu. Save Session… will save to the current active session (thereby overwriting it) whereas Save Session As… will open the Save As dialog for you to choose your own file name.
Session information is written to a file with a “.ysav” extension. SQLyog employs a SQLite database to keep track of the last saved file’s location.
Automatic and manually saved Session Savepoints can be employed simultaneously by SQLyog. If automatic session restoration is enabled, the last active session will be loaded on startup. Then, any saved Session Savepoints can be accessed via the Open Session Savepoint… command.
Once a saved Session Savepoint has been opened, it may be manually closed by choosing the End Session command from the File menu. That causes SQLyog to stop tracking activity for that session. It will, however, continue to track the session for automatic session restoration if that option is activated.
If for instance, you are a consultant working with many clients, this is a highly useful feature. You can have many savepoints for each client and get access to all the connections & scripts with a single click. And you won’t confuse what belongs to client A with what belongs to client B.
Autocomplete
Autocomplete, or word completion, is a software feature where the application predicts the rest of a word a user is typing. It’s one of the most coveted features in both development and administration software for a couple of very good reasons:
- It speeds up human-computer interactions by providing one or more words that the user might intend to enter after only a few characters have been typed into a text input field.
- It reduces typos by inserting the exact text of the object that the user wishes to access.
With respect to database administration software like SQLyog, the purpose of the autocomplete feature is to facilitate the insertion of database tables and columns names as well as MySQL keywords and functions into queries and DDL statements.
Available in SQLyog Professional, Enterprise and Ultimate editions, the Autocomplete feature is made possible by the use of a small database engine that is built right into SQLyog. It contains the names of MySQL keywords and functions and for every connection that it uses. It also maintains a list of the object names for each connection for fast retrieval.
In addition to the two general benefits of Autocompletion stated above, there are very real and tangible benefits when dealing with long and/or awkward object names that are often found in RDBMSes. By reducing the possibility of misspellings, there is far less risk of accidentally executing incorrect queries and reaching erroneous conclusions.
Autocomplete basically works in four independent and differing ways.
‘Auto-popup’ or ‘tooltip’
Whenever you type an object name followed by a period (.), a list of available child objects will pop up in a small window. You can then select an item from the list by:
-
- using the up and down arrow keys to move the selection highlighting, and then,
- pressing the TAB or ENTER key to select the item.
For instance, try typing a database name like “sakila.” to bring up the list of tables within the sakila database:
Moreover, once you’ve selected a table, entering another period (.) will bring up the popup list once again with a list of columns within that table:
The Autocomplete feature also works with functions. For example, typing the letters “sub” followed by the Ctrl + SPACE key combination will bring up the popup list with functions that match your typed letters:
Typing the name of a function followed by an opening parenthesis “(“ will display the parameter list of that function. Continuing with our previous example, typing “substr(“ will bring up the parameters for the substr() function:
In this case, there are actually four signatures for the substr() function, each with a different parameter list. You can iterate through each of these using the up and down arrows. As you do so, the parameters will change.
The current parameter is highlighted in red.
While writing a routine call the parameter list will highlight the current parameter in red.
The Ctrl+Shift+Space Shortcut
If for whatever reason, the parameter list goes away, such as when invoking a different autocomplete feature, the Ctrl+Shift+Space keyboard shortcut will always display the parameter-list of a routine when the cursor is positioned inside it.
TAB-key functionality
Highlighting a database in the Object Browser and typing in one or more characters in the editor pane will activate the TAB key to act as an object iterator. For every press of the TAB key, the autocomplete to iterate over all possible inputs that start with that character combination. Inputs are a combination of functions, keywords and database objects from the selected database.
For example, say that the film table of the sakila database is selected in the Object Browser. After typing in a value of “fil” in the editor, pressing the TAB key once will select the first matching word – in this case “film”. Pressing the TAB key a second time will change the word to the next matching word – in this instance “film_actor”. Once the desired word is present on the screen, simply continue typing from there.
CTRL+ENTER functionality.
Similar to the TAB-key functionality, pressing the CTRL+ENTER key combination after typing one or more characters in the editor pane will bring up a small window containing a list of keywords and functions starting with that character combination. You can then use the arrow-keys to navigate to the one you want. Pressing ENTER inserts the selected entry.
CTRL+SPACE functionality
Whenever the cursor is placed in the editor pane pressing CTRL+SPACE always opens a small window with a list of ALL possible keywords and functions. As above use the up and down arrow-keys to select an item and then press ENTER to insert the selected item.
Using Autocomplete with Aliases
Since version 6.5 Autocomplete supports table aliases and column aliases.
In the above image, you will see that all Autocomplete functionalities identify ‘f’ as an alias of the film table.
Autocomplete supports both table and column aliases in SELECT, UPDATE and DELETE statements except in the following cases, which are not supported at this time:
- SELECT statements used to specify a value for INSERT such as:
"INSERT INTO mytable (col1) values ((SELECT ...))" - SELECT statements used as an argument to a function such as:
"SELECT IF((SELECT ....),'true','false')"
Column-width Persistence
The Result Tab displays the data from SELECT queries in a Grid view by default. It formats the results in Excel-like rows and columns. (Form and Text views are also available.) The Grid view offers a few benefits, such as sorting: Clicking on the header sorts the results by that column. This sorting is done on the client side without sending any additional queries to the MySQL server.
In SQLyog, the Grid view offers another advantage.
With most other GUI tools using a Data GRID you will need to spend time adjusting the width of column headers every time you open the program. In SQLyog, column widths are persistent across sessions and even across master/slave replicas of same database.
Conclusion
In this blog, we explored the many advantages offered by Database administration tools, in particular, those provided by SQLyog. Its unique features include:
- Session Restore recalls your previous session along with all the query tabs, query builder tabs, schema designer tabs and the history tab just the way they were before a sudden outage or application shut down.
- Session Savepoints allow for the manual saving and restoring of sessions to any number of save point states.
- Available in SQLyog Professional, Enterprise and Ultimate editions, the Autocomplete feature displays suggestions for table, view, column, and routine names, along with their parameters.
It reduces the possibility of misspellings, there is far less risk of accidentally executing incorrect queries and reaching erroneous conclusions.
Autocomplete works in four ways:
-
- Whenever you type an object name followed by a period (.), a list of available child objects will pop up in a small window.
- Highlighting a database in the Object Browser and typing in one or more characters in the editor pane will activate the TAB key to act as an object iterator. For every press of the TAB key, the autocomplete to iterate over all possible inputs that start with that character combination. Inputs are a combination of functions, keywords and database objects from the selected database.
- Similar to the TAB-key functionality, pressing the CTRL+ENTER key combination after typing one or more characters in the editor pane will bring up a small window containing a list of keywords and functions starting with that character combination.
- Whenever the cursor is placed in the editor pane pressing CTRL+SPACE always opens a small window with a list of ALL possible keywords and functions.
Since version 6.5 Autocomplete supports table aliases and column aliases.
- With most other GUI tools using a Data GRID, you will need to spend time to adjust the width of column headers every time you open the program. In SQLyog, column widths are persistent across session and even across master/slave replicas of the same database.
SQLyog not only delivers more robust operation than free database administration tools, but also comes with a variety of useful features that help you get the most out of your DBA tasks. Download SQLyog free trial.
