MySQL 5.7 vs MySQL 8.0 – What’s new in MySQL 8.0?

MySQL is arguably the most popular and common RDBMS. The last major release was MySQL 5.7, back in 2013.

As you probably heard, MySQL 8.0 was released to the public, and with it there are some very exciting new enhancements. MySQL 8.0 can be downloaded from MySQL's official website.

So without further ado, let's dive into listing the new comers to MySQL 8.0:

• Roles - Very exciting feature that allows to create roles in MySQL server and assign specific privileges to them. These roles can be assigned to users. So from now you don't have to remember which permissions a programmer from team X needs, and should a QA from team Y needs privilege Z. Also, it's very easy to set up:
  • Creating a new role:

    CREATE ROLE 'app_developer', 'app_read', 'app_write';

  • Assigning privileges to roles:

    GRANT SELECT ON app_db.* TO 'app_read';

  • Assigning the role to a user:

    GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';

• Invisible index - ever wanted to just hide an index you currently don't need, without actually dropping it? Now you can. If you're not sure if you need an index, you can mark it as invisible and MySQL optimizer won't use it. After monitoring for your server and queries performance, you can decide to re-activate it in case you believe it will result in a performance boost.
• Configuration Persistence - Changing configuration during MySQL runtime is commonly done using SET GLOBAL. This disadvantage in this technique is that the changes will not survive a server restart. here comes SET PERSIST to the rescue, which does exactly that, apply configuration changes which survive a MySQL server restart.
• Default character set and collation - Starting MySQL 8.0, the default character set will be utf8mb4 and the collation will be utf8mb4_800_ci_ai. These are great news and one step ahead towards standardized multilingual support in data driven applications.
• UUID Enhancements - UUIDs are usually used to generate unique ids in tables. Starting this new version, MySQL can hold these values in a VARBINARY(16) column instead of CHAR(36). The impact of this change is better storage usage and performance improvement. Also, three new functions were introduced to handle these UUID values: BIN_TO_UUID(), UUID_TO_BIN(), IS_UUID().
• Cost Model Improvements - for the first time, MySQL's cost model will look into the memory and check if the relevant data for the query already resides in memory. As a result of this change, different query plans can be chosen. this will happen automatically without need for extra configuration.
• Descending Indexes - MySQL now allows to create descending indexes and scan them in a reverse order, without performance penalty. This was possible in the past, but you would have to take the performance hit on that.
• Common Table Expressions - CTE is a new feature (which is already available in other databases) that will simplify the way you write complex queries. To put it in simple words, using this feature (the WITH select) will automatically create a temporary table behind the scenes, which you can use in the same query and refer to it. This temporary table / view can only be used in that single query. This feature introduces both readability and performance improvements.

As you can see, there are many great enhancements introduced by the MySQL team in version 8.0. Keep tuned for more information in our next blog posts.