In MySQL Server 8.0.23 an improvement was made to InnoDB that allows truncation and deletion of all tablespaces to be faster. In fact, making it almost instantaneous as long as AHI is disabled. This is done by allowing pages in the buffer pool to be freed lazily instead of immediately when a tablespace is deleted.…

Facebook Twitter LinkedIn

On behalf of the Vitess maintainers team, I am pleased to announce the general availability of Vitess 9. Major Themes # In this release, we have focused on making Vitess more stable after the successful release of Version 8. There have been no major issues reported. So there were no patches released for Version 8. This has allowed us to push further on compatibility and adoption of common frameworks as priorities.

Adieu MySQL 5.6!

When you arrived in 2013 you had a lot of cool new features.  There was the NoSQL Memchache plug-in that was blazingly fast, an improved Performance Shema, full texted searching for InnoDB, big improvements in the optimizer, and great enhancements to replication. You were a great replacement for 5.5!

But in a few days you become history, a museum piece.  No more updates for big fixes for you.  You will become part of the 'back in the old MySQL 5.6' days stories senior DBAs and developers will tell.  You were a big improvement over 5.5 and a stepping stone to 5.7 & 8.0.  

You arrived with the mysql_config_editor, started the SHA256 password options, and you let us do some table alterations online.  

But now you time is passed and the MySQL Community Moves onward.  A lot of us old timers will hear your echoes in MySQL 8 and the newer users will not …

 JOINs confuse a lot of those new to Structured Query Language (SQL). If you read the various web forums popularly used to ask questions then you know that using JOINs is pretty scary for a lot of folks. The sheer number of the 'how do I use JOIN to link two tables together?' questions is staggering. Not just because the JOIN itself is often obfuscated with relational algebra, Venn Diagrams, and the syntactical oddities of SQL -- and that is when you do find an online resource that tries to help. Plus adding to the frustration on top of all that are that the various options for using JSON can be downright perplexing.

Example 1

Let us start with an example of customers and their orders.  There is one table named customer for the information pertaining to the customer. There is another table with order information named orders holding the details of any orders from those folks in the customer table.  …

MySQL membership conditions are in the MySQL 8 Documentation. They’re found in the 13.2.11.3 Subqueries with ANY, IN, or SOME section. The IN and =ANY operators both perform equality matches with one twist. The IN operator works with a set of values or a subquery but the =ANY operator only works with a subquery.

I created the digits, letters, and words tables for this example. They hold the following values respectively:

• The numbers table holds the values of 1, 2, 3, and 4
• The letters table holds the values of 'a', 'b', 'c', and 'd'
• The words table holds the values of 'Captain America', 'Iron …

There was an excellent question from Francis on my MySQL X DevAPI tutorial on how to work with NULL values:

Is it possible to store an attribute with the null value, and how to find it (xxx is null ?

It may sound like a trivial question, but the answer is not all that simple, so instead of just replying directly to the comment, I think it is worth covering the subject of NULL values in the X DevAPI in a separate post.

The Short Story

In short there are two ways to work with “not a value” in the X DevAPI. One way is to not set a value for those fields in which case you can compare the retrieved values with IS NULL as you would for a relational table, for example in MySQL Connector/Python:

# Find documents where "Birthday IS NULL"
stmt_find = …

We recently saw how the new Invisible Column feature works in MySQL since 8.0.23 and how we can use it as a Primary Key for InnoDB tables when no Primary Key was defined.

As I wrote earlier, a good Primary Key is important for InnoDB (storage, IOPS, secondary indexes, memory…) but there is another important domain where a Primary Key is important in MySQL: replication !

Asynchronous Replication

When using “traditional” replication, if you modify a record (UPDATE and DELETE), the record(s) to modify on the replica are identified using indexes, and of course the Primary Key if any. The hidden global 6-bytes auto generated by InnoDB primary key is never used as never exposed and as it’s global, …

Although how range_optimizer_max_mem_size operates is explained in the official doc, it’s not a well-known variable and surely not a variable you need to modify under most circumstances. But from time to time we get some performance tickets related to this.

What problem does this variable cause if it is not properly sized? Let’s find out with an example!

Given the following table definition:

CREATE TABLE `joinit` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=latin1

With ~2M rows

select count(*) from joinit ;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+

With the default value of  range_optimizer_max_mem_size = 8388608, the following …

… an expedition to uncover (and fix) database performance issues!

© 2021 Tiago L. Alves. All rights reserved.

One of the worse things that can happen when upgrading to a newer database version is discovering that the performance is not as good as before. Despite the effort put into gate-keeping MySQL NDB Cluster’s strict performance requirements, one of our customers found a performance regression when upgrading from our 7.4 version to our 7.6 version. How did that happen when our automated performance test suite failed to show it? To answer that, and fix the issue we enrolled on a database performance archaeology expedition…

When your performance is not good enough

MySQL NDB Cluster is an open-source in-memory distributed database developed for high-availability (99.999% or more) and predictable query times. It can be found at the core of gaming, banking, telecommunication, and online services. …

This article is the second part of the series related to MySQL Invisible Column started here.

This post covers why Invisible Column is important for InnoDB Storage Engine.

To start, let me explain briefly how InnoDB deals with Primary Key and why an good primary key is important. And finally, why having a Primary Key is also important.

How does InnoDB Stores Data?

InnoDB stores data in table spaces. The records are stored and sorted using the clustered index (the primary key): they are called index-organized tables.

All secondary indexes also contain the primary key as the right-most column in the index (even if this is not exposed). That means when a secondary …