I really enjoyed to dig into the solution I described yesterday in this post, to generate table audit information using invisible columns and triggers.

In this post, I will focus only on the solution using a JSON column to store the audit information.

Yesterday, I wrote that it’s also possible to track all changes an not only the last one but also keep information about what changed.

I wanted to illustrate that with an example, let’s start with the output:

This is exactly what I was looking for !

And of course we can search in that audit information. For example let’s search for all records that have been modified and where the old or new name was/is frederic:

We can verify that indeed that record had frederic as initial value.

Triggers

Compare …

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 …

VRGlass's customer success story explains why they chose MySQL HeatWave over AWS to increase performance in query execution and analytics and improve their computing power to support their large-scale events.

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 …