How do you run non-blocking schema changes in MySQL? This is an eternal question. With a plethora of 3rd party solutions and with recent advancements in MySQL, it's difficult to track which solution is preferable for a given schema migration. In this post, we provide a high level overview of the state of MySQL online schema migrations in 2024. We limit the discussion to ALTER TABLE statements, as other DDL statements are typically fast (DROP TABLE is somewhat of an exception, but out of scope of this post).

We'll first examine the native MySQL options: INPLACE and INSTANT. For reference, see Online DDL Operations MySQL 8.0 documentation.

…

pt-online-schema-change is an amazing tool for assisting in table modifications in cases where ONLINE ALTER is not an option. But if you have foreign keys, this could be an interesting and important read for you. Tables with foreign keys are always complicated, and they have to be handled with care. The use case I am […]

Introduction # I recently encountered an intriguing bug. A user reported that their query was causing vtgate to fetch a large amount of data, sometimes resulting in an Out Of Memory (OOM) error. For a deeper understanding of grouping and aggregations on Vitess, I recommend reading this prior blog post. The Query # The problematic query was: selectsum(user.type)fromuserjoinuser_extraonuser.team_id=user_extra.idgroupbyuser_extra.idorderbyuser_extra.id;The planner was unable to delegate aggregation to MySQL, leading to the fetching of a significant amount of data for aggregation.

Recently MySQL added the ability to write Stored Programs in JavaScript.  This allows developers to leverage JavaScript capabilities for complex data processing and business logic within the database server. We think this is really cool. Here’s why. Why JavaScript? It is ubiquitous It has multiple runtimes It has a huge development eco-system What’s great about […]

Recently MySQL added the ability to write Stored Programs in JavaScript.  This allows developers to leverage JavaScript capabilities for complex data processing and business logic within the database server. We think this is really cool. Here’s why.

MySQL 9.0 is an Innovation Release, but as you may have read in my Quick Peek, I found little innovation. However, one new item caught my eye, and it could be a way to track query performance. MySQL 9.0 EXPLAIN enhancement Here is the syntax that will be referenced in the post. {EXPLAIN | DESCRIBE […]

In this article, we will demonstrate how to have a single MySQL database user account that can connect from specific hosts. We would usually implement it by creating separate user accounts with the same username but different hosts/IPs like <USER>@<HOST1>, <USER>@<HOST2> …. <USER>@<HOSTn>. Then, give those users the same grants(privileges/roles) and settings(password, SSL, etc). Instead […]

Regular expressions are a powerful tool for developers. In this post we will demonstrate how to use regular expressions subexpressions in MySQL

As I have discussed, regular expressions can be a powerful tool for developers. When we use regular expressions to match a pattern and need to replace part of that pattern, subexpressions make it much easier. Subexpressions are specific parts of a pattern that we can reference elsewhere in the process. The Requirement The requirement for […]

MySQL is certainly a powerful open source database management system, but even the most robust engine struggles when queries take an eternity to execute. For DBAs and developers, improving MySQL query performance is an ongoing goal. Efficient query performance is crucial for ensuring the smooth operation and optimal user experience of applications powered by MySQL […]