Feb
02
2009
Profile and Format MySQL queries with the New SQLyog 8.0
Posted by Webyog on Mon 02 Feb 2009 14:30 UTC
Tags:

Hi,

SQLyog 8.0 is a major new version of SQLyog introducing major features like Query Profiler, SQL Formatter and vastly improved look and feel.

Query Profiler:

MySQL has always lacked the sophisticated profiling tools shipped with proprietary databases like SQL Server, etc. MySQL developers have largely depended on EXPLAIN for tuning queries. The SHOW PROFILE patch by Jeremy Cole was introduced in the MySQL Community version 5.0.37 and it provided much more insight into where the query spends its time. However, to take advantage of this feature, MySQL developers were supposed to switch on profiling, run their queries and then filter the profiling data from a table that contained the profiling results of the last few profiled queries. A lot of manual book-keeping is required to take advantage of this  powerful feature. In an ideal situation, the MySQL developer should execute the queries and the profiling info should be available along with the result-set. Unfortunately, none of the MySQL client tools (desktop or web-based) provide intrinsic support for this feature.

Another great tool to profile queries is mk-query-profiler, part of maatkit. It does an incredible job in displaying the changes in STATUS variables due to execution of a query. It also presents the information in a very readable format, making it easy for the developer to tune queries. However, one problem with this tool is the lack of integration with any MySQL GUI client tool. So if an user is using a GUI tool to write queries, she needs to profile queries in a separate step using mk-query-profiler.

SQLyog 8.0 tries to address these problems by integrating all of the above profiling methods in a single intuitive GUI. It takes care of switching on profiling and taking the snapshot of the STATUS variables before and after the execution of query. It then aggregates these data (along with EXPLAIN and EXPLAIN EXTENDED results) and presents the aggregated info in a single window. The user just needs to type the query as usual and all other commands required to capture profiling information are executed behind the scenes by SQLyog. Also, along with the changes in the STATUS variable, the profiler shows a simple description of the STATUS variables that changed due to execution of the query.

In summary, the SQLyog’s Query Profiler helps the user get more insight into the execution of a query by:

  • Using SHOW PROFILE information
  • Capturing the difference in STATUS variables due to execution of a query
  • Using EXPLAIN statement, and
  • Using EXPLAIN EXTENDED statement

Embedded below is the screencast on how Query Profiler helps you in optimizing the queries.

Screencast on Query Profiler from Team Webyog.

SQL Formatter:

There is absolutely no doubt that properly formatted SQL code makes SQL much more maintainable. Most experienced SQL developers make sure that they follow some SQL formatting guidelines to make their code readable and maintainable. However, many times we come across unformatted SQL written by others or sometimes we are too lazy to write properly formatted SQL ourselves! SQLyog 8.0 comes to the rescue! It neatly formats the SQL with the click of a button. This feature is also known as “SQL Beautifier” or “SQL Pretty Printer” in various other software.

SQL Formatter in action

New look and feel, improved icons:

Though SQLyog has added tons of features to its quiver, it looks the same as it did in 2004. So, we thought of overhauling its looks. Result was adding new icons with Vista theme, toolbar containing large icons for frequently used tasks, gradient based tab and grids, etc. Here is a screenshot of SQLyog 8.0.

The All New SQLyog 8.0

Finally, for a complete list of features and bug fixes in SQLyog 8.0, please refer to the following posts. (Note that SQLyog 8.0 was previously named as SQLyog 7.5. Due to the inclusion of many major features it is being released as SQLyog 8.0.)

Note: For few hours past this announcement you may receive registration code for 7.x when purchasing 8.0. If the code does not work please get the right one form Customer Portal.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Regards,
Team Webyog