SQL - GROUP BY query optimization

Question

I am having some troubles trying to optimize the definition of a view in my db.

The query sums 2 kind of quantities based on LEFT JOIN of 3 tables, described as the stocking view within this fiddle: SQL Fiddle

Basically I tried to create indexes or re-define the view, but every time the execution time is ~5.5s even if the larges of the tables - the rows table in the fiddle - has ~15K records.

The thing that I'm struggling to understand is why, even if an index is defined for the GROUP BY column statement - shown in the possible_keys result for the EXPLAIN command - no index is used.

--- EDIT ---

I think that I've found my issue, the use of LEFT JOIN instead of INNER JOIN on the head table and the rows table was slowing the query down due to MySQL internal design Also usign a coverage index on the head table for ID and CLOSING_DATE better tune up the performaces

DDLs AND RECORDS

CREATE TABLE head (
  ID varchar(30) NOT NULL,
  CLOSING_DATE int NOT NULL,
  PRIMARY KEY(ID),
  KEY CLOSING (ID, CLOSING_DATE)
);

CREATE TABLE `rows` (
  ID int NOT NULL,
  HEAD_ID varchar(30) NOT NULL,
  PRODUCT varchar(30) NOT NULL,
  QTY decimal(16,6) NOT NULL,
  STKQTY decimal(16,6) NOT NULL,
  PRIMARY KEY(ID),
  KEY PROD(PRODUCT),
  KEY HEAD(HEAD_ID)
);

CREATE TABLE production (
  ID int NOT NULL,
  ROW_ID int NOT NULL,
  QTY decimal(16,6) NOT NULL,
  PRIMARY KEY(ID),
  KEY ROWID(ROW_ID)
);

CREATE VIEW row_prod AS(
  SELECT ROW_ID, SUM(QTY) AS PRD_QTY FROM production WHERE ROW_ID <> 0 GROUP BY ROW_ID
);

CREATE VIEW stocking AS(
  SELECT 
    `rows`.PRODUCT,
    SUM(`rows`.STKQTY) AS STKQTY,
    GREATEST(SUM(CASE WHEN CLOSING_DATE <> 0 THEN 0 ELSE (`rows`.QTY - `rows`.STKQTY - COALESCE(row_prod.PRD_QTY, 0)) END), 0) AS RESQTY
  FROM
    `rows`
  INNER JOIN
    head ON head.ID = `rows`.HEAD_ID
  LEFT JOIN
    row_prod ON row_prod.ROW_ID = `rows`.ID
  GROUP BY
    `rows`.PRODUCT
);

INSERT INTO head (ID, CLOSING_DATE) VALUES 
 (1, 0),
 (2, 20220601),
 (3, 0);

INSERT INTO `rows` (ID, HEAD_ID, PRODUCT, QTY, STKQTY) VALUES 
 (1, 1, 'PROD_A', 10, 2),
 (2, 1, 'PROD_B',  5, 0),
 (3, 2, 'PROD_A', 15, 6),
 (4, 2, 'PROD_D',  7, 0),
 (5, 2, 'PROD_E', 12, 4),
 (6, 3, 'PROD_A',  3, 0),
 (7, 3, 'PROD_B',  1, 1),
 (8, 3, 'PROD_F',  5, 5),
 (9, 3, 'PROD_G',  6, 6);

INSERT INTO production (ID, ROW_ID, QTY) VALUES 
 (1, 1, 1),
 (2, 3, 6),
 (3, 4, 10);

EXPLAIN DETAILS

id|select_type|table     |partitions|type   |possible_keys   |key     |key_len|ref            |rows|filtered|Extra                                        |
--+-----------+----------+----------+-------+----------------+--------+-------+---------------+----+--------+---------------------------------------------+
 1|PRIMARY    ||          |ALL    |                |        |       |               |   6|   100.0|                                             |
 2|DERIVED    |head      |          |index  |PRIMARY,CLOSING |CLOSING |96     |rows.HEAD_ID   |   3|   100.0|Using index; Using temporary; Using filesort |
 2|DERIVED    |rows      |          |ref    |HEAD,PROD       |HEAD    |92     |head.ID        |   1|   100.0|                                             |
 2|DERIVED    ||          |ref    |                |        |4      |rows.ID        |   2|   100.0|                                             |
 3|DERIVED    |production|          |index  |ROWID           |ROWID   |4      |               |   3|   100.0|Using where                                  |

SQL - GROUP BY query optimization

0 Answers