May 2024
M T W T F S S
 12345
6789101112
13141516171819
20212223242526
2728293031  

Categories

May 2024
M T W T F S S
 12345
6789101112
13141516171819
20212223242526
2728293031  

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 of today, several development milestones were declared for MySQL v8.0, but there is no official release available yet.

Tip: Do not click here if all your SQL queries are fast as lightning!

Even though there is no official software release to download and use yet, the list of features for MySQL 8.0 was officially announced, which makes it a great point in time to talk about it and share these great enhancements with you.

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.

I’m very excited and look forward to have see this software version released. Many great enhancements are introduced by the MySQL team in v8.0. Keep tuned for more information soon.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>