Abstract:

By diving into the details of our case study, we will explain how incorrect table statistics may lead the optimizer to choose a suboptimal execution plan. We will also go into how MySQL calculates the table statistics and the ways to correct the table statistics to prevent it from happening again.

Case study: Incorrect table statistics lead the optimizer to choose a poor execution plan.

A customer reported a drastic performance degradation of a query while there were no code changes and no configuration changes made. The data in this article has been edited for brevity and modified to mitigate the exposure of confidential information. The case has also been approved for publication by the customer.

We obtained the query execution plan, and got the results as shown below (execution plan #1):

mysql> explain 
-> SELECT count(con.id)          ,
->        MAX(DAYNAME(con.date)) ,
-> …

Today, somebody asked me how he could track the creation of a record (who created it and when) and who and when it was last modified.

Usually, this is performed in a different table, called audit table. In MySQL you can create an audit table and populate it using triggers.

But of course, this person had some constraints, otherwise it would have been too easy:

1. no other table could be used/created
2. the application should keep working without any changes

He was desperate for help…

MySQL 8.0 Invisible Columns

The answer is : Invisible Columns.

Let’s have a look at a simple table, containing an ID as primary key, a first name and a last name. And of course the application must work as expected… and unfortunately, it seems the application is …

In this blog post, you will learn how to create a MySQL table using the various tools MySQL Workbench provides without typing any SQL code. Continue reading and learn how…

Image by Larisa Koshkina from Pixabay 

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

No MySQL Code? …

Last month I performed a review of the Percona Operator for MySQL Server which is still Alpha.  That operator is based on Percona Server for MySQL and uses standard asynchronous replication, with the option to activate semi-synchronous replication to gain higher levels of data consistency between nodes. 

The whole solution is composed as:

Additionally, Orchestrator (https://github.com/openark/orchestrator) is used to manage the topology and the settings to enable on the replica nodes, the semi-synchronous flag if required. While we have not too much to say when using standard Asynchronous replication, I want to write a few words on the needs …

Last month I performed a review of the Percona Operator for MySQL Server (https://www.percona.com/doc/kubernetes-operator-for-mysql/ps/index.html) which is still Alpha.  That operator is based on Percona Server and uses standard asynchronous replication, with the option to activate semi-synchronous  replication to gain higher levels of data consistency between nodes. 

The whole solution is composed as:

Additionally, Orchestrator (https://github.com/openark/orchestrator) is used to manage the topology and the settings …

Last month I performed a review of the Percona Operator for MySQL Server (https://www.percona.com/doc/kubernetes-operator-for-mysql/ps/index.html) which is still Alpha.  That operator is based on Percona Server and uses standard asynchronous replication, with the option to activate semi-synchronous  replication to gain higher levels of data consistency between nodes. 

The whole solution is composed as:

Additionally, Orchestrator (https://github.com/openark/orchestrator) is used to manage the topology and the settings …

In this article, we are going to talk about the WHERE clause and how to use it in MySQL. Besides SELECT, the scope of the WHERE clause includes the UPDATE and DELETE statements. The WHERE clause is indispensable for quality work with MySQL databases. That’s why the current article will deal with this clause and […]

The post WHERE Condition in MySQL with 16 Different Query Examples appeared first on Devart Blog.

Hey there. Thanks for being here for another issue of the OpenLampTech, the newsletter for PHP/MySQL developers. Plenty of great content in issue #21. Enjoy!

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

I learn so much by curating each OpenLampTech newsletter issue. I hope you get as much value out of it as I do in creating it. Here’s what’s in store in …

Apache Airflow is an open-source workflow management platform for data engineering pipelines.

Today we will see how we can deploy very easily Apache Airflow on a Compute instance in Oracle Cloud Infrastructure (OCI) using MySQL HeatWave Database Service (MDS).

I am sharing Terraform modules you can use to deploy in your architecture:

Architectures

With the modules you can deploy architectures like these:

with a single MDS instancewith MDS High Availabilitywith HeatWave Cluster

We will use the exact same modules built into a stack for OCI’s Resource Manager. This will allow us to deploy the architecture just in few clicks.

Deployment

We open in a browser the following GitHub repository: …

Amazon Web Services (AWS) very recently(March 02, 2022) announced the GA of its new RDS feature “Readable standby with Multi-AZ deployments” for MySQL. Yes !! you heard it right you can now use the standby instances created with Multi-AZ deployments for failover as well as for Read-scaling starting with version 8.0.26 and later for MySQL in RDS

Launching a MultiAZ Cluster

Now let us see how to launch this readable-Multi AZ cluster?

Region Availability: As this is a new feature now it is currently limited to the regions US-EAST-1 (N.Virginia), US-WEST-1 (Oregon), and EU-WEST-1 (Ireland), this list would be extended progressively

VPC requirement:

Before launching the instance, you should have SUBNET created for 3 AZ(Availability Zone) within the VPC since the cluster instances would be spawn across 3AZ by default

Hereunder the “Engine Option” …