11. MySQL Workbench – MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds

© Jesper Wisborg Krogh 2020
J. W. KroghMySQL 8 Query Performance Tuninghttps://doi.org/10.1007/978-1-4842-5584-1_11

11. MySQL Workbench

Jesper Wisborg Krogh1 
(1)
Hornsby, NSW, Australia
 

MySQL Workbench is Oracle’s graphical user interface for querying and managing MySQL Server. It can be seen as one of the two Swiss army knives for working with MySQL, with the other being MySQL Shell that is discussed in the next chapter.

The main feature of MySQL Workbench is the query mode where you can execute queries. There are however also several other features such as the performance reports, Visual Explain, the ability to manage the configuration and inspect the schema, and more.

If you compare MySQL Workbench with MySQL Enterprise Monitor, then MySQL Enterprise Monitor is dedicated to monitoring and is a server solution, whereas MySQL Workbench is a desktop solution which is primarily a client for working with MySQL Server. Similarly, the monitoring that is included in MySQL Workbench is all ad hoc monitoring, whereas MySQL Enterprise Monitor as a server solution includes support for storing historical data.

This chapter will introduce MySQL Workbench and go through installation, basic usage, and how to create EER diagrams. The performance reports and Visual Explain will be covered in later chapters.

Tip

If you are already familiar MySQL Workbench, you can consider skipping this chapter or skim it.

Installation

You install MySQL Workbench in the same way as other MySQL programs except there is only support for using the package manager (thus no standalone installations). The MySQL Workbench version numbers follow the MySQL Server versions so that MySQL Workbench 8.0.18 is released at the same time as MySQL Server 8.0.18. A MySQL Workbench version supports the MySQL Server versions that are still being maintained at the time of release, so MySQL Workbench 8.0.18 supports connecting to MySQL Server 5.6, 5.7, and 8.

Tip

It is best to use the latest MySQL Workbench release. You can see the compatibility of the MySQL tools at https://dev.mysql.com/doc/mysql-compat-matrix/en/.

This section will show examples of how to install MySQL Workbench on Microsoft Windows, on “Enterprise Linux 7” (Oracle Linux, Red Hat Enterprise Linux, and CentOS), and on Ubuntu 19.10. Other Linux platforms are similar in concept to the two Linux examples.

Tip

If you are a MySQL customer, it is recommended to download MySQL Workbench from Patches & Updates in My Oracle Support (MOS). This will give you access to the commercial version of MySQL Workbench which has some extra features such as an audit log inspector and a graphical user interface for MySQL Enterprise Backup (MEB).

Microsoft Windows

On Microsoft Windows, the preferred way to install MySQL Workbench is to use MySQL Installer for Windows. If you have other MySQL products installed, you may already have MySQL Installer installed in which case you can skip the first steps of these instructions and instead click Add on the main screen which takes you to the point of Figure 11-5.

You can download MySQL Installer from https://dev.mysql.com/downloads/installer/. Figure 11-1 shows the download section.
Figure 11-1

The MySQL Workbench download page

There are two choices for the installer. The first called the web installer (mysql-installer-web-community-8.0.18.0.msi) is just MySQL Installer, whereas the second (mysql-installer-community-8.0.18.0.msi) also includes MySQL Server. If you plan to install MySQL Server as well, it makes sense to choose the download that includes both MySQL Installer and MySQL Server as you avoid waiting for the installer to download the MySQL Server installation files later. This example assumes you choose the web installer.

You click the Download button to access the download. If you are not logged in, it will take you to the Begin Your Download page where you can choose between logging in and starting the download straight away. This is shown in Figure 11-2.
Figure 11-2

The second step in downloading MySQL Workbench

If you already have an account, you can sign in. Otherwise, you can choose to sign up to an Oracle account. You can also choose to download the installer without logging in by clicking the No thanks, just start my download link.

When the download has completed, launch the downloaded file. Other than confirming that you will allow the installer and MySQL Installer to modify the installed programs, there are no actions required to install MySQL Installer. Once the installation has completed, MySQL Installer automatically launches and detects MySQL programs already installed using an MSI installer as shown in Figure 11-3.
Figure 11-3

The MySQL Installer detects previously installed MySQL programs

If you do not have any MySQL programs installed, you are taken to a screen that asks you to confirm that you agree with the license terms. Please read the license terms carefully before proceeding. If you can accept the license, tick the I accept the license terms check box and click the button labelled Next ➤ to continue.

The next step is to choose what to install. The setup type selection screen is shown in Figure 11-4.
Figure 11-4

The MySQL Installer setup type chooser

You can choose between several bundles such as the developer bundle (called Developer Default) which installs the products typically used in a development environment. When you choose a setup type, the description in the right of the screen includes a list of the products that will be installed. For this example, the custom installation type will be used.

The next step is to choose which products to install. That uses the selector shown in Figure 11-5.
Figure 11-5

Select what to install

You find MySQL Workbench in the available products list under Applications. Click the arrow pointing to the right to add MySQL Workbench to the list of products and features to be installed. Feel free to choose additional products; for this book, it is recommended to also include MySQL Shell. When you have added all the products you need, click Next ➤ to proceed.

The following screen provides a summary of the products that will be installed. Click Execute to start the installation. The installation process includes downloading the product if MySQL Installer does not already have a local copy. The installation may take a little while to complete. When it has completed, click Next ➤ to continue. The final screen lists the installed programs and gives you the option to launch MySQL Workbench and MySQL Shell. Click Finish to close MySQL Installer.

If you later want to install more products or perform upgrades or remove products, you can launch MySQL Installer again which takes you to the main MySQL Installer screen as shown in Figure 11-6.
Figure 11-6

The main MySQL Installer screen

You choose the action you want to perform in the rightmost part of the screen. The actions are
  • Add: Install products and features.

  • Modify: Change the installation of an existing product. This is mainly useful for MySQL Server.

  • Upgrade: Upgrade a product that is already installed.

  • Remove: Uninstall a product.

  • Catalog: Update MySQL Installer’s list of available MySQL products.

These five actions allow you to perform all steps required during the lifecycle of the MySQL products.

Enterprise Linux 7

If you are using Linux, you install MySQL Workbench using the package manager. On Oracle Linux, Red Hat Enterprise Linux, and CentOS 7, the preferred package manager is yum as it will help resolve dependencies of the packages that you install or upgrade. MySQL has a yum repository for its community products. This example will show how to install that and use it to install MySQL Workbench.

You can find the URL to the repository definition at https://dev.mysql.com/downloads/repo/yum/. There are also repositories for APT and SUSE. Choose the file that corresponds to your Linux distribution and click Download. Figure 11-7 shows the file for Enterprise Linux 7.
Figure 11-7

The repository definition download for Enterprise Linux 7

If you are not logged in, it will take you to a second screen like in the example of installing MySQL Workbench on Microsoft Windows. This will allow you to log in to your Oracle Web account, create an account, or download without logging in. Either download the RPM file and save it in the directory you want to install it from or right-click the Download button (if you are logged in) or the No thanks, just start my download link and copy the URL as shown in Figure 11-8.
Figure 11-8

Copying the link to the repository installation file

You can now install the repository definition as shown in Listing 11-1.
shell$ wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
...
HTTP request sent, awaiting response... 200 OK
Length: 26024 (25K) [application/x-redhat-package-manager]
Saving to: 'mysql80-community-release-el7-3.noarch.rpm'
100%[=========================>] 26,024      --.-K/s   in 0.001s
2019-08-18 12:13:47 (20.6 MB/s) - 'mysql80-community-release-el7-3.noarch.rpm' saved [26024/26024]
shell$ sudo yum install mysql80-community-release-el7-3.noarch.rpm
Loaded plugins: langpacks, ulninfo
Examining mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch
Marking mysql80-community-release-el7-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-3 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=================================================================
 Package
      Arch   Version
                   Repository                               Size
=================================================================
Installing:
 mysql80-community-release
      noarch el7-3 /mysql80-community-release-el7-3.noarch  31 k
Transaction Summary
=================================================================
Install  1 Package
Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-3.noarch        1/1
  Verifying  : mysql80-community-release-el7-3.noarch        1/1
Installed:
  mysql80-community-release.noarch 0:el7-3
Complete!
Listing 11-1

Installing the MySQL community repository

MySQL Workbench requires some packages from the EPEL repository. On Oracle Linux 7, you can enable it like
sudo yum install oracle-epel-release-el7
On Red Hat Enterprise Linux and CentOS, you need to download the repository definition from Fedora:
wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
sudo yum install epel-release-latest-7.noarch.rpm
You are now able to install MySQL Workbench as shown in Listing 11-2.
shell$ sudo yum install mysql-workbench
...
Dependencies Resolved
================================================================
 Package        Arch   Version      Repository             Size
================================================================
Installing:
 mysql-workbench-community
                x86_64 8.0.18-1.el7 mysql-tools-community  26 M
Transaction Summary
================================================================
Install  1 Package
Total download size: 26 M
Installed size: 116 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/mysql-tools-community/packages/mysql-workbench-community-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Public key for mysql-workbench-community-8.0.18-1.el7.x86_64.rpm is not installed
mysql-workbench-community-8.0.18-1.         |  31 MB  00:14
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql80-community-release-el7-3.noarch (@/mysql80-community-release-el7-3.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-workbench-community-8.0.18-1.el7.x86   1/1
  Verifying  : mysql-workbench-community-8.0.18-1.el7.x86   1/1
Installed:
  mysql-workbench-community.x86_64 0:8.0.17-1.el7
Complete!
Listing 11-2

Installing MySQL Workbench on Enterprise Linux 7

Your output will likely look different, for example, depending on which packages you already have installed, dependencies may be pulled in. The first time you install a package from the MySQL repository, you will be asked to accept the GPG key used to validate the downloaded packages. If you installed the EPEL repository from Fedora, then you will also need to accept the GPG key from that repository.

Debian and Ubuntu

Installing MySQL Workbench on Debian and Ubuntu follows the same principles as in the previous example. For the steps demonstrated here, Ubuntu 19.10 will be used.

Tip

See https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ for the full documentation on using the MySQL APT repository.

For Debian and Ubuntu, you need to install the MySQL APT repository for which the definition file can be downloaded from https://dev.mysql.com/downloads/repo/apt/. At the time of writing, there is just one file available – see Figure 11-9 – which is architecture independent and works for all supported Debian and Ubuntu versions.
Figure 11-9

The APT repository configuration file

If you are not logged in, you will be taken to the screen where you can choose between logging in and starting the download straight away. Either download the DEB package or right-click the Download button (if you are logged in) or the No thanks, just start my download link and copy the URL as shown in Figure 11-10.
Figure 11-10

Copying the link to the repository installation file

You can now install the MySQL repository as shown in Listing 11-3.
shell$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.14-1_all.deb
...
Connecting to repo.mysql.com (repo.mysql.com)|23.202.169.138|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 35564 (35K) [application/x-debian-package]
Saving to: 'mysql-apt-config_0.8.14-1_all.deb'
mysql-apt-config_0. 100%[==================>]  34.73K  --.-KB/s    in 0.02s
2019-10-26 17:16:46 (1.39 MB/s) - 'mysql-apt-config_0.8.14-1_all.deb' saved [35564/35564]
shell$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb
Selecting previously unselected package mysql-apt-config.
(Reading database ... 161301 files and directories currently installed.)
Preparing to unpack mysql-apt-config_0.8.14-1_all.deb ...
Unpacking mysql-apt-config (0.8.14-1) ...
Setting up mysql-apt-config (0.8.14-1) ...
Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)
OK
Listing 11-3

Installing the DEB package definition

During the second step (the dpkg -i command), you can choose which MySQL products should be available through the repository. The screen where this is set up is shown in Figure 11-11.
Figure 11-11

Package configuration for the MySQL APT repository

The default is to enable MySQL Server and Cluster as well as the tools and connectors. For MySQL Server and Cluster, you can also choose which version you want to use with the default being 8. In order to install MySQL Shell, you need to ensure that MySQL Tools & Connectors is set to be enabled. Select Ok when you have made your changes.

Before you can start to use the repository, you need to execute the update command for apt-get:
shell$ sudo apt-get update
Hit:1 http://repo.mysql.com/apt/ubuntu eoan InRelease
Hit:2 http://au.archive.ubuntu.com/ubuntu eoan InRelease
Hit:3 http://au.archive.ubuntu.com/ubuntu eoan-updates InRelease
Hit:4 http://au.archive.ubuntu.com/ubuntu eoan-backports InRelease
Hit:5 http://security.ubuntu.com/ubuntu eoan-security InRelease
Reading package lists... Done
You can now install MySQL products using the install command for apt-get. Listing 11-4 shows an example of installing MySQL Workbench (notice that the package name is mysql-workbench-community – the “-community” at the end is important).
shell$ sudo apt-get install mysql-workbench-community
Reading package lists... Done
Building dependency tree
Reading state information... Done
...
Setting up mysql-workbench-community (8.0.18-1ubuntu19.10) ...
Setting up libgail-common:amd64 (2.24.32-4ubuntu1) ...
Processing triggers for libc-bin (2.30-0ubuntu2) ...
Processing triggers for man-db (2.8.7-3) ...
Processing triggers for shared-mime-info (1.10-1) ...
Processing triggers for desktop-file-utils (0.24-1ubuntu1) ...
Processing triggers for mime-support (3.63ubuntu1) ...
Processing triggers for hicolor-icon-theme (0.17-2) ...
Processing triggers for gnome-menus (3.32.0-1ubuntu1) ...
Listing 11-4

Installing MySQL Workbench from the APT repository

The output is quite verbose and includes a list of changes to other packages that are required to install MySQL Workbench. The list of packages depends on what you have installed already.

You are now ready to start using MySQL Workbench.

Creating Connections

The first time you launch MySQL Workbench, you will need to define the connection to the MySQL Server instance. If you have MySQL Notifier1 installed, MySQL Workbench will automatically create a connection for the root user to each instance monitored by MySQL Notifier.

You can also create connections as needed. One option is to do this from the MySQL Workbench connections screen which is shown in Figure 11-12.
Figure 11-12

The MySQL Workbench connections screen

The connections screen is accessed by clicking the icon at the upper left showing a database with a dolphin. The icon below with the tables connected by lines takes you to the database modeling feature, and the last of the three icons opens a tab for the data migration feature.

The screenshot shows the connections screen with the welcome message and with one connection already present. You can right-click the connection to access the options for the connection – these include opening the connection (creating a connection to the MySQL instance), editing the connection, adding it to a group, and more.

You add a new connection by clicking + to the right of MySQL Connections. The dialog for configuring a connection is shown in Figure 11-13. The dialogs for creating a new connection and editing an existing one are very similar.
Figure 11-13

The dialog for creating a new connection

You can name the connection with a name of your choice. It is a free-form string that is just used to make it easier to identify the purpose of the connection. The rest of the options are the usual connection options.

Once you have your connection, you can double-click it from the connections screen to create a connection.

Using MySQL Workbench

The most used feature in MySQL Workbench is the ability to execute queries. This is done from the query tab which includes several features in addition to the ability to execute the queries. These features include showing the result set, obtaining a visual representation of the query plan called Visual Explain, getting context help, reformatting queries, and more. This section will look at some of the features starting with an overview.

Overview

The query tab consists of two areas with one being an editor where you write your queries and the other the query result. There are also support for showing context help and query statistics. These two additional areas are technically not part of the query tab, but since they are mostly used with the query tab, they will also be discussed here.

Figure 11-14 shows MySQL Workbench with the query tab and with the most important features numbered.
Figure 11-14

MySQL Workbench and the query tab

The area marked as ① is where you write your queries. You can keep several queries here, and MySQL Workbench will save them, so they are restored when you open the connection again. This makes it convenient as a scratch pad to store your most frequently used queries.

You execute the query or queries using one of the three lightning icons marked as ②. The left icon is a plain lightning symbol and executes the query or queries selected in the query editor part. This is the same as using the keyboard shortcut Ctrl+Shift+Enter. The middle icon with the lightning symbol and a cursor executes the query where the cursor is. Using this icon is the same as using shortcut Ctrl+Enter while in the editor. The third icon has a magnifier in front of the lightning symbol and creates the query plan in form for the query where the cursor is currently placed. The default way to display the query plan is as a Visual Explain diagram. You can also obtain the query plan by using the keyboard shortcut Ctrl+Alt+X.

The result is displayed below the query editor ③, and you can choose between several formats by using the items to the right of the query result. The last of these items is Execution Plan ④ which brings up the query plan for the query in the same way if you had requested it directly from the query editor.

Below the query tab is the output frame ⑤ which by default shows statistics for the last executed query. This includes when the query was executed, the query, the number of rows found, and how long it took to execute it. To the right there is a frame with SQL additions ⑥ which by default shows context help. You can enable automatic context help or request it manually using the icons above the help text.

Configuration

There are several settings that can be changed for MySQL Workbench ranging from the colors to the behavior and paths to programs such as mysqldump that MySQL Workbench depends on.

There are a couple of ways to get to the settings as shown in Figure 11-15. The figure shows the upper-left and upper-right part of the MySQL Workbench window.
Figure 11-15

Accessing the MySQL Workbench preferences

In the left-hand side, you can get the preferences from the menu by using Edit and go to the Preferences item at the bottom. Alternatively, you can click the gear icon in the right-hand side of the window. Either way, you get to the preferences pop-up that is shown in Figure 11-16.
Figure 11-16

The MySQL Workbench preferences

The General Editors settings include settings such as the SQL mode to consider for the syntax checker and whether to use spaces or tabs for indentation. The SQL Editor settings include whether to use the safe settings, whether to save the editors, and the general behavior of the editor and query tab. The Administration settings specify the paths to use including for mysqldump if you do not want to use the bundled binary. The Modeling settings are for the database modeling feature. The Fonts & Colors settings allow you to change the visual appearance of MySQL Workbench. The SSH settings are used when you use a feature that requires an SSH connection to a remote host. Finally, the Others settings include a few settings that do not fit in the other categories such as whether the welcome message should be displayed on the start screen with the connections.

The settings include safe settings. What are those?

Safe Settings

MySQL Workbench has two safe settings enabled by default to help prevent changing or deleting all rows in a table and to avoid fetching too many rows. The safety settings mean that UPDATE and DELETE statements are blocked if they do not have a WHERE clause, and SELECT statements have LIMIT 1000 added (the maximum number of rows can be configured). The WHERE clause for UPDATE and DELETE statements cannot be a trivial one.

Caution

Do not become complacent just because the safety settings are enabled. UPDATE and DELETE statements can still do a lot of damage with a WHERE clause, and a SELECT query with LIMIT 1000 can still require MySQL to examine many more rows.

It is usually best to leave these settings enabled, but for some queries you will need to change the settings for them to work as expected. The SELECT limit can be changed in the settings as just described. The limit is set under the SQL Execution submenu under SQL Editor. Alternatively, an easier way is to use the drop box above the editor as shown in Figure 11-17.
Figure 11-17

Changing the SELECT limit

Changing the limit this way updates the same setting as if you go through the preferences.

The UPDATE and DELETE safe setting can be changed in the SQL Editor settings furthest down. It is recommended to keep it on unless you really need to update or delete all rows in a table. Note that disabling the setting requires reconnecting.

Reformatting Queries

One nice feature of MySQL Workbench that does not usually get a lot of attention is the query beautifier tool. This can also be useful for query tuning as a well-formatted query can make it easier to understand what the query is doing.

The query beautifier takes a query and splits the select list, tables, and filters into separate lines and adds indentation. An example of this is shown in Figure 11-18.
Figure 11-18

The query beautifier feature

The first query is the original query with the whole query in a single line. The second query is the reformatted query. For a simple query like in this example, the beautification is of little value, but for a more complex query, it can make the query much easier to read.

The beautification by default includes changing SQL keywords to uppercase. You can change whether that should happen in the Query Editor submenu of the SQL Editor settings in the preferences.

EER Diagrams

The last feature that will be explored is the support for reverse engineering a schema and creating an enhanced entity-relationship (EER) diagram. This is a useful way to get an overview of the schema you are working with. If foreign keys have been defined, MySQL Workbench will use the definitions to link the tables together.

You can start the reverse engineering wizard from the Database menu option and then choose Reverse Engineering. Alternatively, the Ctrl+R keyboard combination will also take you there. This is shown in Figure 11-19.
Figure 11-19

Opening the reverse engineering feature

The wizard will take you through the steps to import the schema starting with choosing which of your stored connections to use or optionally configuring the connection manually. The next step connects and imports a list of the available schemas which are shown in the third step. Here you choose one or more schemas to reverse engineer as shown in Figure 11-20.
Figure 11-20

Choosing which schemas to reverse engineer

In this example, the world schema has been chosen. The next steps fetch the schema objects and allow you to filter which objects to include. Finally, the objects are imported and placed in the diagram, and a confirmation is shown. The resulting EER diagram is shown in Figure 11-21.

Tip

If MySQL Workbench crashes when creating the diagram, try opening Edit ➤ Configuration… ➤ Modelling in the menu and check the Force use of software based rendering for EER diagrams option.

Figure 11-21

The EER diagram for the world database

The diagram shows the three tables in the world database . When you hover over a table, the relations to the other tables will be highlighted in green for a child table and in blue for a parent table. This allows you to quickly explore the relations between the tables to give you knowledge that can be crucial when you need to tune queries.

Summary

This chapter introduced MySQL Workbench which is MySQL’s graphical user interface solution. It was shown how to install MySQL Workbench and create connections. Then an overview of the main query view was given, and it was shown how you can configure MySQL Workbench. By default, you cannot execute UDPATE and DELETE statements without a real WHERE clause, and SELECT queries are limited to 1000 rows.

Two features that were discussed are query beautification and EER diagrams. These are not the only features, and later chapters will show examples of the performance reports and the Visual Explain query plan diagrams.

The next chapter will discuss MySQL Shell which is the second of the two “Swiss army knives” provided by MySQL.