In this article, I want to discuss a couple of pretty new features in MySQL 8.0 — and an older one. Maybe these are minor features you are not aware of, and maybe not so relevant, to be honest. But it is worth providing a quick overview, showing how they work, and how they could be useful in some cases.

All refer to the invisibility of something:

• Invisible columns
• Generated invisible primary keys
• Invisible indexes

Let’s take a look.

Invisible columns

The invisible columns feature has been deployed since version 8.0.23. What is an invisible column? It’s basically a regular column of a table with its own name and data type. It is treated and updated as any other regular column, with the only difference being that it is invisible to the application. In other words, it can be accessed only in the case it is explicitly addressed in your SELECT; otherwise, it is …

Dynamic SQL is a desirable feature that allows developers to construct and execute SQL statements dynamically at runtime. While MySQL lacks built-in support for dynamic SQL, this article presents a workaround using prepared statements. We will explore leveraging prepared statements to achieve dynamic query execution, parameterized queries, and dynamic table and column selection.

Understanding prepared statementsMySQL support for server-side prepared statements, leveraging the efficient client/server binary protocol. A prepared statement is a functionality designed to execute identical or similar SQL statements repeatedly, achieving optimal efficiency in database operations.

Advantages of Prepared Statements

1. Reduced Parsing Overhead: Prepared statements minimize the overhead of parsing SQL queries each time they are executed. This is especially advantageous in database applications that …

In this blog post, we will describe the improvements to Percona XtraBackup 8.0.33-28 (PXB), which significantly reduces the time to prepare the backups before the restore operation. This improvement in Percona XtraBackup significantly reduces the time required for a new node to join the Percona XtraDB Cluster (PXC).

Percona XtraDB Cluster uses Percona XtraBackup to do SST (State Snapshot Transfer) from one node to another. When a new node joins the cluster, SST is performed to receive the data from DONOR to the JOINER. JOINER uses PXB to stream the data directory from DONOR. JOINER must prepare the backup before using it. It is observed that when the DONOR has a huge number of tablespaces (one million),  XtraBackup on JOINER …

Well, “easy” if you know just a tiny bit of C++.

MySQL is well known for its ease of use, being easy to install, easy to configure, and easy to maintain. What if there is something more that you’d like MySQL to do? How would you integrate some new fancy processing library into MySQL without having to recreate the complexities in pure SQL?

MySQL Loadable Functions would be the way to go. In this blog post, you’ll learn how to set up a build environment for compiling your own MySQL plugin to be loaded into MySQL as a function. Our function will implement a ULID generator using a C++ library from ChrisBove/ulid.

Creating the build environment

The first step is downloading the …

This blog was originally published in January 2022 and was updated in July 2023.

Working with hundreds of different customers, I often face similar problems around running queries. One very common problem when trying to optimize a database environment is index usage. A query that cannot use an index is usually a long-running one, consuming more memory or triggering more disk iops.

A very common case is when a query uses a filter condition against a column that is involved in some kind of functional expression. An index on that column can not be used.

Starting from MySQL 8.0.13, functional indexes are supported. In this article, I will first explain an overview of indexes in MySQL and cover the MySQL CREATE INDEX before diving into showing what functional indexes are and how they work.

Introduction to MySQL Indexes

Fragmentation is a common concern in some database systems. Highly fragmented tables can affect performance and resource allocation. But reducing fragmentation often involves rebuilding the table completely. This blog post will discuss fragmentation and its impact on InnoDB.

What is fragmentation?

We say that something is fragmented when it is formed by parts that are separate or placed in a different order than the natural one. In databases, we can experiment with different types of fragmentation:

• Segment Fragmentation: segments are fragmented; they are stored not following the order of data, or there are empty pages gaps between the data pages.
• Tablespace Fragmentation: the tablespace is stored in non-consecutive filesystem blocks.
• Table Fragmentation: data is stored not following the primary key order (heap tables), …

Updated June 1, 2023.

Suboptimal MySQL ORDER BY implementation, especially together with MySQL LIMIT is often the cause of MySQL performance problems. Here is what you need to know about MySQL ORDER BY LIMIT optimization to avoid these problems.

Try Now: Free your applications with Percona Distribution for MySQL

MySQL LIMIT clause

The MySQL LIMIT clause is a valuable tool for controlling the number of rows returned by a SELECT statement. By specifying the maximum number of rows to retrieve from the result set, it enables you to work with subsets of data, especially in situations involving large tables. This feature enhances query performance and optimizes resource usage by fetching only …

MySQL 5.7 reaches End of Life status this October. If you still need to start your migration, time is getting short. The first step for many is looking into the new reserved words in MySQL 8.0. As MySQL gets new functionality or the project matures, there are new additions to the list of reserved words you can not use as column names.

Reserved words added to 8.0

There is a list of the new reserved words later in this document that you need to review.

Odds are you are not using a column named Master_tls_ciphersuites, but what about Rank, System, Skip, or Lead? Those are a lot more common and may be in your table definitions, so your upgrade process will be harder. It is recommended that you use the util.checkForServerUpgrade() in the MySQL Shell to check for these Reserved Words.

So what happens if I use a reserved word?

In MySQL Server 8.0.32, Oracle fixed Bug #105761:

“mysqldump make a non-consistent backup with ‐‐single-transaction option” (this commit)

which caused a wave of complaints from users who could no longer do backups with the mysqldump utility because of the lack of the required privileges.

• Bug #109701 “Fix for #33630199 in 8.0.32 introduces regression when ‐‐set-gtid-purged=OFF”
• Bug #109685 “mysqldump has incompatible change in MySQL 8.0.32″

…

You might want to use binlog compression with MySQL/Percona Server for MySQL, but it can come with drawbacks. This article discusses the need for using binlog compression and the potential issues it may cause.

Binlog compression is a technique used to reduce the size of binary log files, which can become quite large over time. This can be especially important in situations where disk space is limited. However, it’s important to be aware that using binlog compression can also cause issues with replication.

Consider the following scenario: you have restored a backup that was taken from a replica node using Percona XtraBackup. Once the restoration is complete, you want to set up replication using the information from the xtrabackup_slave_info …