MySQL rewriteBatchedStatements configuration property
Introduction In this article, we are going to see how MySQL rewriteBatchedStatements works when using JDBC, JPA, or Hibernate. I first researched this MySQL configuration property when I was writing the batching chapter of my High-Performance Java Persistence book, and, back then, I discovered that this setting allows batching plain Statement by rewriting the SQL string that is sent to the database. However, the MySQL 6 Connector/J documentation mentioned that: for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option So, for a long time, I wrongly… Read More
How to index JSON columns using MySQL
Introduction In this article, I’m going to explain how we can index JSON columns when using MySQL. While other relational database systems provide GIN (Generalized Inverted Index) indexes, MySQL allows you to index a virtual column that mirrors the JSON path expression you are interested in indexing.
MySQL Query Profiling Using Performance Schema
Introduction In this article, I’m going to explain how to do query profiling using the MySQL Performance Schema. The Performance Schema has been available since MySQL 5.5.3 and allows MySQL to instrument SQL queries and store their trace information in various tables that you can later inspect in order to determine why a given SQL statement is slow. On older versions of MySQL, you might have used the SHOW PROFILE command, but since this feature has been deprecated, you should now use the Performance Schema for SQL query profiling.
MySQL JDBC Statement Caching
Introduction In this article, we are going to see how we can enable the MySQL JDBC Driver Statement Caching mechanism, which, unfortunately, is not enabled by default. No matter what data access framework you are using, you still need to configure the JDBC Driver if you want to get the most out of the MySQL database engine.
Percona PMM – A beginner’s guide
Introduction This article is a beginner’s guide for the Percona Monitoring and Management (PMM), a tool that’s very handy when it comes to analyzing a given database system. PMM was released in 2016, and initially, it was available for MySQL. However, since 2018, it supports monitoring PostgreSQL as well, so once you learn it you can use it with the two most popular open-source relational database systems.
MySQL JSON_TABLE – Map a JSON object to a relational database table
Introduction In this article, I’m going to explain how the MySQL JSON_TABLE function works, and how you can use it to transform a JSON object into a relational database table. When using a relational database system, it’s best to design the database schema according to the relational model. However, there are certain use cases when the relational model is too strict, and we are better off storing data in a JSON column type. For instance, as I explained in this article, when designing an audit log table, it’s much more convenient to… Read More
MySQL audit logging using triggers
Introduction In this article, we are going to see how we can implement an audit logging mechanism using MySQL database triggers to store the old and new row states in JSON column types.
MySQL 8 support for custom SQL CHECK constraints
Introduction In this article, we are going to test the MySQL 8 implementation of custom SQL CHECK constraints. Although the CHECK clause is a standard SQL feature, prior to MySQL 8.0.16, the clause was parsed and ignored, so this functionality could only be emulated via BEFORE INSERT and UPDATE triggers.
How to extract change data events from MySQL to Kafka using Debezium
Introduction As previously explained, CDC (Change Data Capture) is one of the best ways to interconnect an OLTP database system with other systems like Data Warehouse, Caches, Spark or Hadoop. Debezium is an open-source project developed by Red Hat which aims to simplify this process by allowing you to extract changes from various database systems (e.g. MySQL, PostgreSQL, MongoDB) and push them to Apache Kafka. In this article, we are going to see how you can extract events from MySQL binary logs using Debezium.
Book Review – High Performance MySQL (3rd edition)
Introduction I either have time for reading or writing, but not both. Now that the first edition of High-Performance Java Persistence is done, I can catch up on the many books I planned on reading but didn’t have time to do so. In this post, I’m going to review High Performance MySQL by Baron Schwartz, Peter Zaitsev, and Vadim Tkachenkoa, which is a must-read book for anyone working with MySQL.
