Sql

How could the following SQL queries be improved performance wise and otherwise and can you also explain why your change is more optimal?

Please consider, when testing, that your results are not confused by the Query Cache or by reading data from your I/O system which are an order of magnitude faster (Query Cache) or slower (I/O) than the in-memory behaviour.

Table of Contents

• Covering function
• LIKE search
• Covering function twice
• Compare 2 indexed columns
• Function and 2 indexed columns
• Time series problem …

When it comes to SQL Query tuning with customers we often get the slow running SQL query and possibly, in good cases, also the table structure. But very often, for various reasons, we do not get the data.

SQL query tuning on an empty table or a table with only little data is not really fun because either the results of the optimizer have nothing to do with reality or the response times do not really show if your change has improved anything. For example if your query response time before the change was 2 ms …

When you are for too long in business you think you know already everything and you are getting lazy. This happened to me again a few weeks ago. A customer asked me about the SQL Error Log Plugin. First I though he was talking about the MariaDB Error Log or the General Query Log. But then I have learned that there is something “new” I did not know yet…

MariaDB introduced in 5.5.22 (March 2012) a new plugin called the SQL Error Log Plugin. This Plugin collects all the errors which were …

MariaDB/MySQL Stored Language is called SQL/PSM.
There are 4 different types of Stored Language: Stored Procedures, Stored Functions, Triggers and Events.

Stored Procedures

Stored Procedure with a Cursor:

DELIMITER //
CREATE PROCEDURE cleanup(IN pData VARCHAR(48))
BEGIN
  DECLARE vId INTEGER;
  DECLARE vNotFound INTEGER;

  DECLARE cCleanUp CURSOR FOR
    SELECT id FROM test WHERE data = pData;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET vNotFound = 1;
  
  OPEN cCleanUp;
  lGetRecord: LOOP …

The General Query Log in MariaDB is a Log (file or table) that logs every statement sent from the Application to the MariaDB Database Server from the Connect Command to the Quit Command. A simple example you can find here:

bin/mysqld, Version: 10.4.6-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysql.sock
Time                Id Command  Argument
190709 21:27:30   3872 Connect  root@localhost as anonymous on test
                  3872 Query    INSERT INTO test …

A customer reported last month that MySQL does a full table scan (FTS) if a query was filtered by a INT value on a VARCHAR column. First I told him that this is not true any more because MySQL has fixed this behaviour long time ago. He showed me that I was wrong:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `data` (`data`)
) ENGINE=InnoDB; …

In our MySQL trainings and consulting engagements we tell our customers always to use the smallest possible data type to get better query performance. Especially for the JOIN columns. This advice is supported as well by the MySQL documentation in the chapter Optimizing Data Types:

Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better …

MySQL hosting providers can easily loose the overview over their customers and which user or schema is still in use and which not.

The MySQL database becomes bigger and bigger, uses more and more RAM and disk space and the backup takes longer and longer.

In this situation it would be nice to know which MySQL database user has logged in within the last 6 months for example. MySQL database users who did not login within a defined period can be backuped and removed from the production MySQL database.

The …

CREATE TABLE `order` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, name VARCHAR(64) NOT NULL
) ENGINE = InnoDB;

INSERT INTO `order` VALUES
  (NULL, 'Test order 1')
, (NULL, 'Test order 2')
, (NULL, 'Test order 3');


CREATE TABLE pos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, order_id INT UNSIGNED NOT NULL
, name VARCHAR(64) NOT NULL
, amount SMALLINT NOT NULL
, price DECIMAL (6,2) NOT NULL
, status TINYINT NULL
) ENGINE = InnoDB;

INSERT INTO pos VALUES
  (null, 1, 'Schrauben', …

Abstract: In this article we discuss how big is the performance impact of MySQL TRIGGERs compared to application side logging (with INSERT) into a MySQL table.

What was in my mind from the past

A while ago when MySQL released its Stored Language features in v5.0 I have seen a book
[1
] about this topic. In this book was a performance comparison between different implementations of computational tasks, one was done in MySQL Stored Language. The result was, that MySQL Stored Language feature sucks also …

A while ago I did some research about MySQL audit functionality and logon a and logoff triggers. MySQL and MariaDB provide a logon trigger in the form of the init_connect variable but no logoff trigger where most of the work for auditing would be done. When we would have a logoff trigger we could track the login and possibility some activity of a user and implement auditing functionality.

Yesterday when I was looking into the code for an answer to the question of one of my customers this research came into …

Table of Contents

• What is a Materialized View?
• Implement your own Materialized Views
• Refreshing materialized views
• Hands on
• Create your own Materialized View:
• Refresh Materialized View on demand
• Refresh Materialized View immediate
• Materialized Views with snapshotting functionality
• Some performance benchmarks for our Materialized Views:
• Outlook
• Conclusion
• Literature

What is a Materialized View?

A Materialized View (MV) is the pre-calculated (materialized) result of a query. Unlike a simple VIEW the result …