Exercise: Compare storage engine options

  • 20 minutes

In these lab exercises you will compare two different MySQL storage engines:

  • InnoDB
  • Memory

Important

You need your own Azure subscription to complete this exercise. If you do not have an Azure subscription, create an Azure free trial.

To complete these exercises, you must have:

  • Created a resource group (see Module 1, Unit 5).
  • Created an Azure Database for MySQL Flexible Server (see Module 1, Unit 5).
  • Downloaded and installed MySQL Workbench (see Module 2, Unit 5).
  • Downloaded the scripts for this exercise from GitHub.

Tip

If you stopped your Azure Database for MySQL server after completing the last lab, restart it now.

Create a database

  1. Open MySQL Workbench and connect to your Azure Database for MySQL Flexible Server.
  2. Select File, Open SQL Script, and navigate to the EnginesLab1.sql script. Select Open.
  3. Highlight and run the first section - Supported engines. This displays the storage engines supported by your server, and whether the storage engine supports transactions.
  4. Highlight and run the section - Create database. This creates a new user database and selects it as the current database.

Investigate transactions and the InnoDB storage engine

  1. Highlight and run the section - Create an InnoDB table. This creates a new table called Cat.
  2. Highlight and run the section - Insert three rows. This inserts three rows into the Cat table and returns the three rows.
  3. Highlight and run the section - Insert a row within a transaction. The transaction was committed, so there should be four rows.
  4. Highlight and run the section - Insert a row and rollback the transaction. There should still be four rows because we rolled back the transaction.

Investigate transactions and the memory storage engine

  1. Highlight and run the section - Create a Memory table. This creates a new table called CatM.
  2. Highlight and run the section - Insert and display three rows. This inserts three rows into the Cat table and returns the three rows.
  3. Highlight and run the section - Insert a row within a transaction. The transaction was committed, so there should be four rows. This appears to work, but the Memory storage engine doesn't support transactions, so it ignores the presence of a transaction.
  4. Highlight and run the section - Insert a row and rollback the transaction. There should still be four rows because we rolled back the transaction. Note the warning in the Output window for ROLLBACK. There are now five rows. The Memory storage engine doesn't support transactions and ROLLBACK had no effect.

Understand the memory storage engine

Create a database

  1. Open MySQL Workbench and connect to your Azure Database for MySQL Flexible Server.
  2. Select File, Open SQL Script, and navigate to the EnginesLab2.sql script.
  3. Highlight and run the section - Create database. This creates a new user database and selects it as the current database.

Understand the memory storage engine

  1. Highlight and run the section - Create a table using Memory storage engine.
  2. Highlight and run the section - Populate the table. The query returns three rows in the resultset.
  3. Use the Azure portal to restart the Azure Database for MySQL flexible server.
  4. Highlight and run the section - The table is empty. Note that table has no rows, but the table object still exists.
  5. Highlight and run the section - Repopulate the table. The query returns three rows in the resultset.
  6. Close MySQL Workbench and in the Azure portal, STOP your Azure Database for MySQL Server.