dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool
14 min read

Learning MySQL introduced me to the command line interface and MySQL Workbench. While I find these tools enough for most tasks, I feel that my productivity is not good enough. So, I looked for another GUI tool and found dbForge Studio for MySQL. To my surprise, it is a Swiss army knife for MySQL.

Before I tell you why, here’s a short background of my journey.

I came from a Microsoft SQL Server background. I code SQL scripts, optimize queries, and do anything in between. And I also code in C# .Net. And I’m used to tools like SQL Server Management Studio and Visual Studio. It was a nice ride.

But I’m not leaving that boat for good. I’m simply expanding my horizons. So, I learned MySQL too. I can install and configure it in Windows and Ubuntu.

Without MySQL Workbench, this is the one I faced in Windows:

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

I use a quite similar interface in configuring MySQL in Ubuntu.

This could be a shocking experience to someone who is used to GUI tools. And someone who dislikes the Terminal or Command-Line. I still use it for quick queries, though. But when I’m stuck and the result set is wide and long, I prefer a GUI tool. So, MySQL Workbench is where I went.

But it’s not like SQL Server Management Studio. But it will do. Visually, I think it’s quite bland for my taste. But that’s just me. Then, came dbForge Studio. It’s the closest to what I’m used to. And I thought adjustment to a new database platform would be faster. And it did.

Here’s why.

Features of dbForge Studio for MySQL

dbForge Studio for MySQL is a versatile product. It’s for database administrators, designers, analysts, and developers. I’m more of a developer. So, I’ll focus more on the developer side. But I will also touch on other features that I used because of the need.

Note that in this review, I used the dbForge Studio for MySQL version 9.0.897 Enterprise Edition.

Here goes.

1. SSMS-like Interface

It’s not a copy of SQL Server Management Studio (SSMS). But the first time you open this tool, it feels like home. The Database Explorer is there. The menu is familiar. The New SQL window with the results pane is more than I expected. The keyboard shortcuts that I remember in SSMS do pretty much the same.

You can change the skin or theme of this tool to make it easy for the eyes. There are 7 skins to choose from:

  • 3 Visual Studio themes (blue, light, and dark)

  • 3 Office themes (Office 2013, Office 2013 Dark Gray, and Office 2013 Light Gray)

  • Bezier

Most of them are more light themes. But I prefer Visual Studio Dark. So, it’s nice to have a dark one.

Overall, screen elements are in the right places where I expect them. Here’s a sample:

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

2. Advanced SQL Coding Features

So, how does it feel coding in dbForge Studio for MySQL? The first time you construct a query by typing, it feels easy with fewer keystrokes.

Why?

There’s IntelliSense and code completion that is context-aware. The syntax is checked as you type. But it won’t get in the way of your typing. It will put red marks or squiggles on keywords that are misplaced. And also objects that don’t exist. It feels like coding in Visual Studio.

So, if you like a Visual Studio way of coding, there’s no awkward moment.

And of course, it knows MySQL syntax. Objects in your database are loaded. So, it knows your every table, column, and key. That’s why it can display a column picker for your SELECT statement. And it can suggest a join to another table.

Here’s a screenshot while coding with a table join suggestion. The skin used is Visual Studio Light.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

If you’re into visual design, there’s a visual Query Builder. With this, you can click more and type less. You’ll see this in action later.

Another favorite is the Query Profiler. It’s not just showing results of EXPLAIN, it records your every execution. It also has statistics for measuring execution time and I/O. So, comparing query executions is a breeze. You’ll see a sample of this later.

And did I mention debugging of stored procedures? You can do that too.

Overall, coding in MySQL is a lot faster.

3. Visual Database Designer

There’s no need for another tool to design your database visually. See tables and columns and their relationships to other tables. All these are within dbForge Studio.

Check out a sample screenshot.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

4. Useful Database Administration Utilities

Admins will find the usual Backup and Restore. But there’s also an Import/Export utility. I used this to get data from SQL Server to MySQL without issues.

Then, there’s Copy Databases. This has proven useful when I migrate databases from Windows to Linux.

And then, there’s Schema Comparison. If you find it hard to compare development and production copies, this is the tool you need. It will list which of the objects are different. Then, you can synchronize them. Below is a screenshot in Visual Studio Light skin.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

Aside from a Schema Comparison, there’s also a Data Comparison.

I’m not a database admin. But for my project needs, the features I mentioned are a lifesaver.

5. Data Reporting and Analysis

Users need and will always ask for ad-hoc reports. Then, dbForge Studio has a Report Designer. You only need a table or a custom query for your report’s data. Here’s a screenshot of the designer:

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

As you can see above, it’s not just text. You can put charts and more into reports. See the Report Toolbox.

Report design starts with a report template or simply a blank canvas. Then, you need to specify the source. It’s either a table or a custom query. Then, the rest are drag-and-drop.

When you’re done with the report design, you can export the output to PDF, Word, Excel, and other formats. Then, you can print it or send it through email.

6. Data Generation

Do you need test data? Every developer needs them. We can’t work on empty tables. So, this is another favorite of mine.

What’s amazing with this is it can generate data that follows your foreign keys. For example, you have a client and insurance policy tables. It generates client names and IDs. Then, these client IDs are used in the policy table.

Here’s a screenshot in Visual Studio Light skin.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

Note that the addresses generated are fictitious.

The Query Designer and Query Profiler in dbForge Studio for MySQL

This part will expand more on the Query Designer and Query Profiler features. But before I made the query, I copied tables and their data. The source is a SQL Server sample database called AdventureWorks. I used the Import tool in dbForge Studio. And it uses an ODBC connection. The following are the tables I copied to MySQL:

  • Production.Product

  • Sales.SalesOrderHeader

  • Sales.SalesOrderDetail

And I put it under the adventureworks2019 MySQL database.

dbForge Studio Query Designer

The Query Designer can form SELECT, INSERT, UPDATE, or DELETE statements. And JOIN, subqueries, groupings, aggregations, filters, and sorting are also possible. Even more, you can use functions and expressions as columns.

To start, click New Query from the toolbar. And a blank design appears. The 3 tables above are the ones we will use. So, drag the 3 tables from the Database Explorer into the blank query design.

Here’s a screenshot of what table columns were selected. And how the tables are related.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

Meanwhile, the 3 tables are INNER JOINed. The arrows between each table show the relationship. And these are the basis of the joins. To replace the JOIN type, click Joins. Then, click Inner Join to select a new JOIN as shown below.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

Next, is the WHERE clause. This will filter the results based on the values or expressions you specify. See a sample below.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

You can use a column within the tables appearing in the diagram. Then, set the operator and a value.

You can optionally add WHERE, GROUP BY, HAVING, and ORDER BY.

Finally, the SQL code generated by this Query Designer is shown below.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

You can run the query the same way you run it without using the Query Designer.

And here’s the result:

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

The amazing thing about the results is you can group them without further coding. If you want to group them by OrderDate, simply drag that column above the rest.

You can also edit the data straight from here and export them.

dbForge Studio Query Profiler

The Query Profiler helps in query optimization. This part will use the query generated by the Query Designer earlier.

To begin, click the Generate Execution Plan from the toolbar. Check the screenshot of the toolbar below to see where that button is.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

The output is divided into 3. First is the Profile. You can see how long the query ran to produce the result set. It took 0.184008 sec. to run the query. Then, above is the breakdown of the execution time and its percentages. Check out the screenshot below.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

So, is this fast enough or not?

To answer that, see the Plan below. It shows the EXPLAIN results of the query.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

This needs some explaining.

  • First, the ALL value under the type column means MySQL did a full table scan for our query. It means it scans all rows on all 3 tables to get the needed result. It is shown on screen.

  • Then, the key and ref columns show why MySQL did a full table scan. There’s no index in all the 3 tables.

  • Finally, thousands of rows were scanned as seen in the rows column. This further confirms the full table scan.

Though the query seems quick with less than 1 sec. duration, the EXPLAIN results show it’s bad. And it’s bad because there’s no index. How did it happen? Because during the import from SQL Server, the indexes are not included.

But the Session Statistics reveal more. See below.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

At this point, we can’t say it’s really bad until we create the indexes and compare.

So, create the indexes and primary keys. Execute the following statements to do that. You can do these too using the GUI.

ALTER TABLE adventureworks2019.`production.product`
ADD PRIMARY KEY (ProductID);
ALTER TABLE adventureworks2019.`sales.salesorderheader`
ADD PRIMARY KEY(SalesOrderID);
ALTER TABLE adventureworks2019.`sales.salesorderheader`
ADD INDEX `IDX_sales.salesorderheader_OrderDate` (OrderDate);
ALTER TABLE adventureworks2019.`sales.salesorderdetail`
ADD PRIMARY KEY(SalesOrderDetailID);
ALTER TABLE adventureworks2019.`sales.salesorderdetail`
ADD INDEX `IDX_sales.salesorderdetail` (SalesOrderID, ProductID);

Then, click the Generate Execution Plan again.

Now, what do we have?

Comparing Execution Time, Plan, and Statistics Using the Query Profiler

This time, we can compare the query performance. This is between tables without indexes vs. those with them. This is a big timesaver.

To do the comparison, press CTRL then left-click the Profile section. And check the duration

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

The query using indexes finished at 0.011 seconds. Meanwhile, querying without table indexes is at 0.221 seconds. Notice the red numbers. That’s the slower one. Though the second attempt with indexes is slower in opening tables. But overall, the indexes improved read execution times.

Let’s see what the Execution Plan says. But this time, you can’t compare them side-by-side. That’s a letdown. So, click each of the Plans to compare. Here’s the new screenshot. You can compare it to the previous plan earlier.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

The compelling part of the plan above is the tremendous decrease in the number of rows read.

The statistics will further prove this. See the side-by-side comparison below. The lower the number, the better.

dbForge Studio for MySQL: Why I Switched to This Easy, All-Rounder GUI Tool

Look at the difference in numbers.

The lesson? Indexes help optimize queries. And dbForge Studio for MySQL lets you see the execution problems better and faster.

What I Liked the Most

The features that made me switch are mentioned above but here’s a summary:

  • Intelligent code completion, syntax check, refactoring, quick object information, and snippets. Everything that can speed up my coding without getting in the way too much is hard to ignore.

  • Query Profiler. Query optimization is important to me. And having a history for side-by-side comparison is just super.

  • Data generation, schema and data comparisons, import/export, and database copy. These non-developer features are simply amazing. It’s good to know it’s there when I need them.

What I Don’t Like

Not all tools are perfect. And dbForge Studio for MySQL also has its shortcomings. Here’s what I don’t like:

  • I tried deleting a primary key constraint in Database Explorer. I chose a table and click Constraints. The primary key column is shown. I pressed the Del key. There’s a prompt asking me if I really want to drop it. I clicked Yes. The primary key disappears along with the column. Since it’s under the Constraints folder and not the Columns folder, I thought it will delete the constraint, not the column. This can be fixed in a future release, though. But be careful. Edit the table instead. And uncheck the primary key. Then, save the table.

  • No side-by-side execution plan comparison. I may be asking too much. But I got spoiled in the Profile and Session Stats. This can be improved in the future too.

  • Missing graphical execution plan.

That’s it.

My overall rating for this product is 9/10. And I recommend this MySQL GUI database developers like me.

Interested? Check out the product page here.

In case you have found a mistake in the text, please send a message to the author by selecting the mistake and pressing Ctrl-Enter.
Comments (0)

    No comments yet

You must be logged in to comment.

Sign In / Sign Up