Author - David Ducos

Finding Table Differences on Nullable Columns Using MySQL Generated Columns

MySQL generated columns

Some time ago, a customer had a performance issue with an internal process. He was comparing, finding, and reporting the rows that were different between two tables. This is simple if you use a LEFT JOIN and an 
IS NULL  comparison over the second table in the WHERE clause, but what if the column could be […]

Read more

Chunk Change: InnoDB Buffer Pool Resizing

innodb buffer pool chunk size

Since MySQL 5.7.5, we have been able to resize dynamically the InnoDB Buffer Pool. This new feature also introduced a new variable — innodb_buffer_pool_chunk_size — which defines the chunk size by which the buffer pool is enlarged or reduced. This variable is not dynamic and if it is incorrectly configured, could lead to undesired […]

Read more

Using Hints to Analyze Queries

Hints to Analyze Queries

In this blog post, we’ll look at using hints to analyze queries.
There are a lot of things that you can do wrong when writing a query, which means that there a lot of things that you can do to make it better. From my personal experience there are two things you should review first:

The table join […]

Read more

Generated Columns and ProxySQL Instead of Referenced Tables

Generated Columns

In this post, we’ll look at how to improve queries using generated columns and ProxySQL instead of implementing a referenced table.
Developers and architects don’t always have the time or complete information to properly analyze and design a database. That is why we see tables with more fields than needed, or with incorrect types. The […]

Read more

Replication Triggers a Performance Schema Issue on Percona XtraDB Cluster

wsrep-stages

In this blog post, we’ll look at how replication triggers a Performance Schema issue on Percona XtraDB Cluster.
During an upgrade to Percona XtraDB Cluster 5.6, I faced an issue that I wanted to share. In this environment, we set up three Percona XtraDB Cluster nodes (mostly configured as default), copied from a production server. We […]

Read more

Testing MySQL partitioning with pt-online-schema-change

There are cases where we need to implement MySQL partitioning, or at least test if it is an option to solve an issue. However, how are we able to test it when the table on our production environment has hundreds of millions of rows, several composite indexes and/or the size on disk is hundreds of gigabytes?
Testing […]

Read more

Speed up GROUP BY queries with subselects in MySQL

We usually try to avoid subselects because sometimes they force the use of a temporary table and limits the use of indexes. But, when is good to use a subselect?
This example was tested over table a (1310723 rows), b, c and d ( 5 rows each) and with MySQL version 5.5 and 5.6.
Let’s suppose […]

Read more

Importing big tables with large indexes with Myloader MySQL tool

Mydumper is known as the faster (much faster) mysqldump alternative. So, if you take a logical backup you will choose Mydumper instead of mysqldump. But what about the restore? Well, who needs to restore a logical backup? It takes ages! Even with Myloader. But this could change just a bit if we are able […]

Read more