This post's content
I recently stumbled upon a very interesting post by Lukas Eder, where he describes 10 query transformations which do not depend on the database’s cost model. He posted it a couple of years ago, though when I read it, I assumed some portions of it may still be relevant today.
In the original post, several databases were tested to see if their internal optimizer will be able to automatically re-write the SQL queries and optimize them. In those tests, MySQL under-performed in several of the use cases (the tested version was MySQL 8.0.2, which was released on 2017-07-17).
Seeing those results, and given the previous evaluation was done almost two years ago, I thought that now can be a good chance to re-evaluate a few of those tests with the latest MySQL 8.0.16 (released on 2019-04-25), and demonstrate EverSQL Query Optimizer's capabilities while we are at it.
We'll use the Sakila sample database for the demonstrations, and some of the original queries from Lukas's post.
The following two tables will be used for these demonstrations:
CREATE TABLE address (
address_id INT NOT NULL,
address VARCHAR(50) NOT NULL,
CONSTRAINT pk_address PRIMARY KEY (address_id)
);
CREATE TABLE customer (
customer_id INT NOT NULL,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
address_id INT NOT NULL,
CONSTRAINT pk_customer PRIMARY KEY (customer_id),
CONSTRAINT fk_customer_address FOREIGN KEY (address_id)
REFERENCES address(address_id)
);
CREATE TABLE actor (
actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id),
KEY idx_actor_last_name (last_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
JOIN Elimination
Looking at the query below, you'll probably notice that the table address isn't really used anywhere in the query (other than in the join's ON clause), and has no actual contribution to the query. The columns are selected from the customer table, and there are no predicates for the address table (or any predicates for that matter). The existence of a PRIMARY KEY for address_id in the table address and the FOREIGN KEY for the same column in the table customer should provide the database's optimizer the confidence to conclude that the join to the table address can be spared.
SELECT c.first_name, c.last_name FROM customer c JOIN address a ON c.address_id = a.address_id
Yet, as you can see from the EXPLAIN, MySQL executes the join, without applying the expected query transformation.
When submitting the same query and schema structure to EverSQL, it will output the following query and recommendation:
In a similar manner, MySQL 8.0.16 will execute the join for all other examples in the Join Elimination section of the original post, so I saw no reason to repeat them here.
Unneeded Self JOIN
In the following query, the table actor is joined to itself. It can be proven that a1 = a2, because the tables are joined using the primary key actor_id. Therefore, anything we can do with a2, can actually be done with a1 as well. Therefore, we can can modify the references to a2 in the SELECT clause to the same columns in a1, and remove the redundant join to a2.
SELECT a1.first_name, a2.last_name FROM actor a1 JOIN actor a2 ON a1.actor_id = a2.actor_id;
MySQL 8.0.16 will execute the join in this case as well, without applying the expected query transformation.
When submitting the same query and schema structure to EverSQL, it will output the following query and recommendation:
Original queries and information for this section can be found here.
Predicate Pushdown
We should always strive to have our SQL queries process as less data as possible, especially if the filtering is done using indexes. In the following query, we expect MySQL to push the condition from the outer query to both parts of the UNION ALL, to make sure we filter out as much data as we can, as early as possible.
SELECT
*
FROM
(SELECT
first_name, last_name, 'actor' type
FROM
actor UNION ALL SELECT
first_name, last_name, 'customer' type
FROM
customer) people
WHERE
people.last_name = 'DAVIS';
As you can see below, the transformation isn't applied by MySQL, and both tables, actor and customer are scanned in full.
Submitting the query and schema structure to EverSQL will result in the following query and recommendation:
When looking at the execution plan of the optimized query, you can see that the indexes are used, and significantly less data is scanned:
Wrapping up
Query transformations can be very powerful, and it's important to understand which of them will be applied automatically by the database and which won't. In this post, we listed three examples (originally posted by Lukas Eder), in which MySQL 8.0.16 didn't apply the expected transformations, which eventually resulted in non-optimal execution plans.
