If you're using MySQL, you likely have indexes that are powered by B-trees. The B-tree is a powerful data structure, and is frequently used to construct indexes in relational databases. If you are using the InnoDB storage engine, it is the only choice for your index, save for spatial indexes. However, MySQL has a secret weapon for making lookups with these types of indexes even faster: the Adaptive Hash Index, or AHI. Before jumping into how this works, let's take a few moments to review B-trees, the InnoDB buffer pool, and how these work together during index lookups.

B-Tree indexes

The B-tree data structure has been used by computer systems for decades. It is particularly useful in the context of file system and data storage applications, due to the fact that each node can store many values. This is useful in …

When talking about storage performance, we often hear three terms that are important in describing storage performance. They are

• bandwidth
• latency
• I/O operations per second (IOPS)

When you talk to storage vendors and cloud providers, they will gladly provide you with numbers on bandwidth and IOPS, but latency numbers will be hard to come by. To evaluate storage, especially for MySQL, you need just one number, and that is the 99.5 percentile commit latency for a random 16 KB disk write. But let’s start at the beginning.

Bandwidth

The bandwidth of an IO subsystem is the amount of data it can read or write per second. For good benchmarking numbers, this is usually measured while doing large sequential writes. A benchmark would, for example, write and read megabyte sized …

A support question: Somebody had a WordPress installation, in which a table had entries with an id column that contained multiple entries with 0. The table was supposed to undergo a schema change where id becomes an actual primary key, and auto_increment. They needed to find all rows WHERE id=0 and assign them unique id values.

Here is a test table:

mysql> create table b ( id integer not null, d varchar(255));
Query OK, 0 rows affected (0.25 sec)

kris> insert into b values (0, "1"), (0, "2"), (3, "3");
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

kris> select * from b;
+----+------+
| id | d    |
+----+------+
|  0 | 1    |
|  0 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

…

When talking about storage performance, we often hear three terms that are important in describing storage performance. They are

• bandwidth
• latency
• I/O operations per second (IOPS)

When you talk to storage vendors and cloud providers, they will gladly provide you with numbers on bandwidth and IOPS, but latency numbers will be hard to come by. To evaluate storage, especially for MySQL, you need just one number, and that is the 99.5 percentile commit latency for a random 16 KB disk write. But let’s start at the beginning.

Bandwidth

The bandwidth of an IO subsystem is the amount of data it can read or write per second. For good benchmarking numbers, this is usually measured while doing large sequential writes. A benchmark would, for example, write and read megabyte sized …

A support question: Somebody had a WordPress installation, in which a table had entries with an id column that contained multiple entries with 0. The table was supposed to undergo a schema change where id becomes an actual primary key, and auto_increment. They needed to find all rows WHERE id=0 and assign them unique id values.

Here is a test table:

mysql> create table b ( id integer not null, d varchar(255));
Query OK, 0 rows affected (0.25 sec)

kris> insert into b values (0, "1"), (0, "2"), (3, "3");
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0

kris> select * from b;
+----+------+
| id | d |
+----+------+
| 0 | 1 |
| 0 | 2 |
| 3 | 3 |
+----+------+
3 rows in set (0.00 sec)

The old, …

Introduction In today’s increasingly complex software landscape, understanding and debugging the inner workings of distributed applications are more challenging than ever before. As applications become more distributed, spanning multiple services and environments, traditional monitoring approaches often fall short of providing comprehensive insights into system behavior and performance. This is where observability comes into play. Observability […]

Yes, but now it likes them more, and here is why. Intro Using the LIKE clause to filter triggers or views from a specific table is common. However, it can play a trick on you, especially if you don’t get to see the output (i.e., in a non-interactive session). Let’s take a look at a […]

This is the second article in our blog series about MySQL NDB Cluster replication, it describes the basic concept and functional parts of a single-channel replication which is used for replicating data between clusters.

A key is typically defined as a column or a group of columns that are used to uniquely locate table records in relational databases (including MySQL, of course). And now that we've covered MySQL primary keys on our blog, it's time to give you a similarly handy guide on foreign keys.

The post How to Add, Show, and Drop MySQL Foreign Keys appeared first on Devart Blog.

The Oracle Cloud Infrastructure (OCI) command line interface (CLI) allows users to manage OCI resources. In this post, we will discuss how to use the OCI CLI to create a MySQL HeatWave configuration that can be specified when creating a new instance.