MySQL Workbench 6.1: Query Result Enhancements

January 29, 2014 – 11:01 pm by akojima

The SQL Editor in MySQL Workbench 6.1 adds a new interface for query results.  This addition offers 4 new views to your query results, 2 for the result data itself and 2 for meta-information about the query that was executed.

Query Result Set Grid

The traditional result set grid. Run a SELECT query on a table with a primary key and you can edit the data. You must click the Edit button to enter edit mode.

Note: Until Workbench 6.1.1, the check was being done automatically for every SELECT query, but since that requires extra queries to MySQL, the check is now done on demand.

 Screenshot 2014-01-29 15.05.44

 

Result Set Form Editor

The new form editor for result sets comes in handy when you want to closely inspect the fields of each record (specially if it has multi-line text). You can also edit the individual records, if your result set is editable.

Screenshot 2014-01-29 15.05.56

Result Set Field Types

Here, you can inspect information about the fields that were returned by the MySQL server in your query results. Similar to the —column-type-info option from the command line client, it will show you the schema and table from where the field comes from and type information.

Screenshot 2014-01-29 15.06.04

Performance Schema Statistics

This tab uses data gleaned from the performance_schema (in MySQL 5.6) to gather some key statistics collected about the execution from your query, as collected by the server. To have this tab, you need to have the performance_schema enabled with statement instrumentation.

Screenshot 2014-01-29 15.14.36

You can read about the meaning of each item in the MySQL performance_schema documentation, but here’s a summary of some key items:

  • Timing: the timing information shown in the Action Output area in Workbench is the query execution time as measured at the client side, so it will include network travel time. But here you also have the timing as instrumented by the server itself. This includes the amount of time waiting for table locks, as a separate value.
  • Rows Processed: the number of rows that were evaluated and then generated to be sent back to the client
  • Temporary Tables: the number of temporary tables that had to be created for the query to be executed
  • Joins per Type: the number of JOINs that were executed for the query, broken down by type. This is similar to the info you’d get from EXPLAIN.
  • Sorting: number of data that had to be sorted by the server.
  • Index Usage: you can see here whether table scans had to be performed without using an index.

You can disable fetching of this information from the Query -> Collect Performance Schema Stats menu item. You may want to do that if you don’t need the stats, since an extra query has to be executed for every query you run.

 

 

Alfredo Kojima

  1. 9 Responses to “MySQL Workbench 6.1: Query Result Enhancements”

  2. Hi,

    it seems i can’t get the result grid to show up when i edit a query… It disapears as i execute it and is nowhere to be found.

    Do you have any idea how to bring it back ?

    In your example, it would be like changing your query to “SELECT * FROM sakila.city WHERE city_id = 1″

    Am i missing something obvious ? Is it a 6.1 bug ?
    Thanks

    By Julien on May 12, 2014

  3. You should be able to drag the result grid back, at the bottom of the editor area. What OS are you using?

    By akojima on Jun 27, 2014

  4. Same here the results grid/panel keeps disappearing everytime I run queries, it is unusable on Mac ….

    By Cloug on Aug 5, 2014

  5. Same issue for me. I use Mac OS X 10.9 and the last version of Workbench (6.1.7.11).

    I’ve just updated from a 6.0 version. Maybe the preferences are corrupted?

    Philippe

    By Philippe on Aug 6, 2014

  6. Found out that restarting the app fixes the issue, but for how long …? (recap: query results panel hides on query execution on Mac OS, very fidgetty : 1px zone for draggin back opened)

    By Bob on Aug 6, 2014

  7. Hi,

    I cannot see the results grid and cannot find it to drag to a bigger size.

    I would expect it to appear on the View menu so I can force it to appear but it isn’t there for some reason.

    How can I make it come back without dragging as there is nothing to drag!

    Thanks,
    Ian

    Version 6.1.7 OSx

    By Ian Lewis on Aug 15, 2014

  8. If you move the mouse to the bottom of the editor area, the cursor will turn to a drag cursor, you can then resize it from there.

    By akojima on Aug 19, 2014

  9. From the Workbench preferences, check Create new tab as Query tabs instead of File (from SQL Editor section).

    By Mario Brouilard on Aug 28, 2014

  10. “Found out that restarting the app fixes the issue, but for how long …? (recap: query results panel hides on query execution on Mac OS, very fidgetty : 1px zone for draggin back opened)”

    That’s right. Thanks !

    By Yuri on Sep 11, 2014

Post a Comment


8 − one =