Coupon code: mysql14
Hurry up, offer expires soon!
Blog

Profile and Format MySQL queries with the New SQLyog 8.0

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 resultsand 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

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.

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

21 thoughts on “Profile and Format MySQL queries with the New SQLyog 8.0

  1. The Query Profiler is a huge feature that just about everyone can find useful. You might want to think about guest blog postings of people showing just how powerful that feature can be. The more examples the better!

  2. The new features are great, but I’m definitely *not* loving the new icons.
    It’s much harder now to find the icon I’m looking for, for instance, the “delete selected row” icon is ridiculously small, with the minus sign WAY too small.

    Is there any way we can get more legible icons, or even restore the *old* look-and-feel, while keeping the same feature set?

  3. @ Jon,

    I’m afraid we cannot revert back to old icons. No offense. You are the first person to complain on the new icons. Rest every one loved the new theme very much. New icons are of 32 bit & contain more details & are more relevant. May be its matter of time & you will get used to it.

    Regards,
    Chirag

  4. I must agree with Jon L that the icons are not that clear as they used to be.
    Could you externalize those icon resources so that people can make their icon sets?

    That would be really nice..

    • @Vanja,

      Thanks for your feedback. Please comment in detail. We will change those icons if required. With the current set up, we cannot externalize the icon set.

      Regards,
      Chirag

  5. hello,
    I found the monitoring tools to be great, lots of great info, etc. etc.
    It told me a lot of things, however, my problem:

    understanding it all AND being able to make changes based on the data provided?

    anyone provide services to make changes?
    and… it gives guidelines, but not understanding everything and not knowing what to change…. well, people who understand this stuff really know what I’m saying.

    thanks

  6. Unfortunatelly, this is a problem with many pieces of software. Their visual “improvements” are indeed very downgrading.

    To be honest, I never understood this trend. Discarding good and working recipe in favour of fancy and often less functional designs. It is becoming more and more the fashion in so-called updates and upgrades. :-(

  7. @Vanja, @Chirag

    I too find the icons to be a pain… I am so used to the old style. Now, being 32bit, what difference does that make? 8bit, 16bit, 32bit… they are just icons/graphics. I am not sure that externalized icons would be the way to go, but i think maybe giving people the option to revert to the 7.5 style set would be a better option.

  8. Such a small thing for you, such a big thing for me…!

    I use PHPEd as an editor. It is really useful to run a query in SQLYog, to see what I have done wrong. However, when I copy the value of the $qry variable, it appears as “UPDATE cookies\r\n\t\t\t\t\t\tSET cokLastVisit = NOW(),\r\n\t\t\t\t\t\t\tcokVisits = cokVisits + 1\r\n\t\t\t\t\t\tWHERE cokNotify = 532065″
    complete with quotes at the beginning and end, as well as newlines, and tabs.

    And SQLYog doesn’t understand that, so I have to put every single query into an editor to reformat it.

    Any chance that a future version could strip out the \t, etc., and run the query?

  9. This PHP editor handles queries/strings as they must appear in PHP code.
    SQLyog editor is a Graphical/GUI editor.

    Any aplication language specific formatting (PHP, JAVA, .NET or whatever) is not supported in SQLyog. SQLyog handles SQL and not application code.

    There are two incompabilities here:
    1) the “quoting” You can (and must)
    2) escaping for linebreaks. In SQLyog in backslash is a backslash simply. You use escaping inside literal strings of a SQL statement (because the MySQL server requires it), but else not! A linebreak is *visual in SQLyog as in GUI editors.
    .. but there can be more examples (for instance also the ” . ” PHP string concatenator would be a problem. In SQLyog and every other GUI editor a DOT is a DOT and not a concatenation symbol).

    We have no plans of supporting application code in SQLyog (and if we had PHP would not be considered more important than C(++), Perl, JAVA, .NET languages etc!)

    You would not be able to find a more compatible PHP development environment?

  10. oops .. typo! I meant this

    There are two incompabilities here:
    1) the “quoting”
    2) escaping for linebreaks. In SQLyog in backslash is a backslash simply .. etc

  11. The problem for me, is that I was told that this would be in the new release, which is why I upgraded.

    I was also told that I would get help if I upgraded the time before, and was simply told that it wasn’t a SQLYog issue (after you had taken my payment)

    So I have upgraded twice, and both time, have not got what I was told I would get. I won’t upgrade a third time!

  12. Pete I am sorry, but I am not able to identify the discussion you are referring to! To me it looks like some kind of misunderstanding.

    I think we never promised support for any other language than (*pure*) SQL. SQLyog is not and was never intended to be a tool for application programming. That does not mean that a utility to strip or interpret application language strings containing SQL to pure SQL is not interesting.

    Also we provide TRIAL builds for evaluation. You had the opportunity to evaluate TRIALs before very purchase.

    Finally such complaints should not be discussed in a public Blog (simply because it has no public interest and discussion may involve privat details).

    I ask you to create a support ticket here: http://www.webyog.com/support

  13. I love SQLYog on the PC. I use it all the time for my “real” job. However, I am really missing a good MySQL GUI for the Mac. Profiling is yet another feature that makes me beg again for WebYog to release a Mac version.

    Thanks,
    Justin Noel
    AppBeacon.com

  14. One issue I’ve found in SQLYog, is that while it will pretty format queries on-the-fly, when you copy the query into another editor, you lose all the nice formatting (at least, as regarding upper/lower-case).

    • @ Jon L,

      Thanks for reporting. We also noticed this aberration during internal testing. You should find it fixed in upcoming releases.

      Regards,
      Chirag

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>