During our MySQL performance review we saw that we had a few full-table scans in our code. We created a view which spans different tables which are Master - detail records. For example, sales and saleslines.
On this master detail record we have the requirement to add paging. So we must be able to fetch the first 10 Sales (and their according sales lines). But also to fetch the first 10 sales which have a salesline where 1 unit-price is higher than 10$.
So we created a view, in this scenario vw_sales, which joins the sale and sale-lines and we are able to meet those requirements.
The Create Tables can be viewed here
CREATE TABLE `sale_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`amount` decimal(20,2) NOT NULL,
`created_on` datetime NOT NULL,
`tenant_id` bigint(20) NOT NULL,
`version` int(11) NOT NULL,
`timestamp` datetime NOT NULL,
`culture` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_sale_on_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `sales_line_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`sale_id` bigint(20) NOT NULL,
`quantity` bigint(20) NOT NULL,
`unit_price` decimal(20,2) NOT NULL,
`offer_product_id` bigint(20) NOT NULL,
`product_id` bigint(20) DEFAULT NULL,
`tenant_id` bigint(20) NOT NULL,
`version` int(11) NOT NULL,
`timestamp` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_sales_line_on_sale` (`sale_id`),
KEY `fk_sales_line_on_product` (`offer_product_id`),
KEY `fk_sales_line_on_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
The view is created as here
CREATE OR REPLACE
ALGORITHM = MERGE
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `vw_sales` AS
SELECT
`sale_test`.`id` AS `id`,
`sale_test`.`amount` AS `amount`,
`sale_test`.`created_on` AS `createdon`,
`sale_test`.`tenant_id` AS `tenantid`,
`sales_line_test`.`id` AS `saleslineid`,
`sales_line_test`.`quantity` AS `quantity`,
`sales_line_test`.`unit_price` AS `unitprice`
FROM
`sale_test` FORCE INDEX (`fk_sale_on_tenant`)
INNER JOIN `sales_line_test` `sales_line_test` ON (`sales_line_test`.`sale_id` = `sale_test`.`id`);
We added the paging like below
SELECT
*,
CASE
WHEN @previous = id THEN @rank
WHEN @previous:=id THEN @rank:=@rank + 1
END AS rank
FROM
vw_sales,
(SELECT @previous:=0) previous,
(SELECT @rank:=- 1) rank
WHERE
id = 26
ORDER BY id ASC
The result with the explain command looks like this
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | "Using filesort" |
| 1 | PRIMARY | <derived3> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | <derived4> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 1 | 100.00 | "Using where" |
| 4 | DERIVED | sale_test | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 4 | DERIVED | sales_line_test | NULL | ref | fk_sales_line_on_sale | fk_sales_line_on_sale | 8 | order_management.sale_test.id | 1 | 100.00 | NULL |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | "No tables used" |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | "No tables used" |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
However we see that due to a limitation in MySql (materialized views) we are causing some full-table scans - table sale_test. (ref. How do I get MySQL to use an INDEX for view query? , Does MySQL view always do full table scan? )
Creating a covering index accross tables doesn't seem to be possible so this options is not possible as well.
We already added the force index inside the view as well to hint the correct index but this doesn't resolve the issue.
How is this being resolved in a descent way? Is creating a temporary table not causing a lot of overhead? This seems so simple but the paging requirements is making it very hard. We are working with mysql 5.7 (AWS RDS) so the window functions added in mysql 8.0 are not an option for us.
FYI: Without the view we see that the index is being used
Query:
SELECT
`sale_test`.`id` AS `id`,
`sale_test`.`amount` AS `amount`,
`sale_test`.`created_on` AS `createdon`,
`sale_test`.`tenant_id` AS `tenantid`,
`sales_line_test`.`id` AS `saleslineid`,
`sales_line_test`.`quantity` AS `quantity`,
`sales_line_test`.`unit_price` AS `unitprice`,
CASE
WHEN @previous = `sale_test`.`id` THEN @rank
WHEN @previous:= `sale_test`.`id` THEN @rank:=@rank + 1
END AS rank
FROM
`sale_test` FORCE INDEX (FK_SALE_ON_TENANT)
INNER JOIN
`sales_line_test` `sales_line_test` ON (`sales_line_test`.`sale_id` = `sale_test`.`id`),
(SELECT @previous:=0) previous,
(SELECT @rank:=- 1) rank
WHERE
`sale_test`.`tenant_id` = 26
Result
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | <derived3> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | sale_test | NULL | ref | fk_sale_on_tenant | fk_sale_on_tenant | 8 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | sales_line_test | NULL | ref | fk_sales_line_on_sale | fk_sales_line_on_sale | 8 | order_management.sale_test.id | 1 | 100.00 | NULL |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | "No tables used" |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | "No tables used" |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+