Top MySQL 8/AWS RDS 3 features that can improve your app

MySQL 8 & AWS RDS 3

Recently AWS announced a long waited support of MySQL 8 for their RDS Aurora. Of course, you could use MySQL 8 engine on RDS way before, but Aurora has lots of good features like replication. Version 5.7 will be supported until Oct 2023 so you should think about migration in advance.

This is my personal list of features that we will be using in our project. I will structure features by their functionality. Here it is:

Performance

1. Instant DDL
   MySQL 8 introduced ALGORITHM=INSTANT with the ALTER TABLE statement. This gives you a huge performance impact on some types of operations. According to official docs ADD COLUMN operation on a table with 150M records would be executed within 0.5–1.5s (!). Compared to MySQL 5.7 — it would take nearly 40m.
   (!) But be aware that this feature currently works only with enabled Amazon Aurora MySQL lab mode
2. Invisible indexes. This can be very useful to test index performance impact without breaking anything on your production database. Adding and deleting indexes on big data sets is expensive and time-consuming. Using an invisible index can help you with testing and after tuning — you can make an index visible to the optimizer. The top feature for big DBs.

Querying

1. Window functions. This super handy feature for analytics was introduced in MySQL 8. It’s completely another way of querying your data. If you don’t know what it is and you doing lots of analytics reports — you definitely have to look at its documentation
2. CTEs using the WITH clause is another handy feature for querying. A common table expression (CTE) is a named temporary result that can be used multiple times within the scope of a statement
3. JSON functions. JSON data type was introduced in MySQL 5.7, but MySQL 8 brought lots of enhancements to JSON. It includes performance upgrades, new functions, etc.

Upgrade experience

Few words about our experience of migration from the 5.7 to 8 version. We were using Aurora compatible with MySQL 5.7 for a while. There are a few ways of migrating but the only one applicable is restoring from a snapshot, due to downtime and data consistency.