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.
SQL Server OPENJSON – Map JSON to a relational table
Introduction In this article, I’m going to explain how the SQL Server OPENJSON function works and how it can help you transform a JSON object into a relational database table. When using a relational database system, it’s a very good idea to design the database schema according to the relational model. However, there are specific situations when the relational model is way too strict, and that’s when a JSON column type might be very handy. For instance, as I explained in this article, when designing an audit log table, it’s much more… Read More
Hash Join Algorithm
Introduction In this article, we are going to see how the Hash Join Algorithm works and when it’s suitable for a relational database system to employ it in order to execute an SQL JOIN query.
Nested Loop Join Algorithm
Introduction In this article, we are going to see how the Nested Loop Join Algorithm works and when it’s suitable for a relational database system to employ it in order to execute an SQL JOIN query.
How to get a JSON property value using PostgreSQL
Introduction In this article, I’m going to explain how you can get a JSON property value using the ->> PostgreSQL operator. This way, we can transform a JSON object that’s stored in a json or jsonb column into a virtual relational database table. While it’s a good idea to design the database schema according to the relational model, there are also situations when the relational model is way too strict. For instance, as I explained in this article, when implementing an audit log mechanism using database triggers, it’s very convenient to store… Read More
SQL LATERAL JOIN – A Beginner’s Guide
Introduction In this article, we are going to see how the SQL LATERAL JOIN works, and how we can use it to cross-reference rows from a subquery with rows in the outer table and build compound result sets. A LATERAL JOIN can be used either explicitly, as we will see in this article, or implicitly as it’s the case for the MySQL JSON_TABLE function.
SQL Server audit logging using triggers
Introduction In this article, we are going to see how we can implement an audit logging mechanism using SQL Server database triggers to store both the previous and the current state of a given target table record in JSON column types.
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
PostgreSQL audit logging using triggers
Introduction In this article, we are going to see how we can implement an audit logging mechanism using PostgreSQL database triggers to store the CDC (Change Data Capture) records. Thanks to JSON column types, we can store the row state in a single column, therefore not needing to add a new column in the audit log table every time a new column is being added to the source database table.
SQL ANY Operator – A Beginner’s Guide
Introduction In this article, we are going to see how the ANY operator works in SQL using it with a subquery or a VALUES clause, as well as its NULL value handling behavior.
