Simplify recursive mysql query

Question

With given MySQL table

CREATE TABLE taxons (
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    parent_id int,
    name varchar(255)
);

The table has following entries

INSERT INTO taxons (parent_id, name) VALUES (NULL, "Baby & Kleinkind"); 
INSERT INTO taxons (parent_id, name) VALUES (1, "Babysicherheit"); 
INSERT INTO taxons (parent_id, name) VALUES (2, "Babysicherungen & Schutzvorrichtungen");

The amount of levels are endless.

I want to select a entity (one taxon) with only one query by traversing the tree down.

Currently my query is kind of handmade

SELECT * 
FROM taxons
WHERE name = "Babysicherungen & Schutzvorrichtungen" 
AND parent_id = (
  SELECT id 
  FROM taxons 
  WHERE name  = "Babysicherheit"
  AND parent_id = (
    SELECT id 
    FROM taxons 
    WHERE name = "Baby & Kleinkind"
  )
);

Are there any better solutions for this query?

See the db fiddle for better understanding: https://www.db-fiddle.com/f/mvMCGdNgjCa9PeNKbbzmRL/0

MySQL v5.7

How many levels do you have? Which is the output you expect? Which version of mysql are you running? — trincot, Jul 22 '19 at 10:52
I created following db fiddle: https://www.db-fiddle.com/f/mvMCGdNgjCa9PeNKbbzmRL/0 — Enthusiasmus, Jul 22 '19 at 10:54

score 1 · Answer 1 · answered Jul 22 '19 at 15:35

A more concise way of expressing this would be to use joins:

select t1.* from taxons t1
join taxons t2 on t1.name = "Babysicherungen & Schutzvorrichtungen" and t1.parent_id = t2.id and t2.name = "Babysicherheit"
join taxons t3 on t2.parent_id = t3.id and t3.name="Baby & Kleinkind"
;

Given the keys you have defined (or lack thereof), it would also be more efficient. See the two EXPLAIN analysis of your SQL and vs. the above:

Schema (MySQL v5.7)

CREATE TABLE taxons (
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    parent_id int,
    name varchar(255)
);

INSERT INTO taxons (parent_id, name) VALUES (NULL, "Baby & Kleinkind"); 
INSERT INTO taxons (parent_id, name) VALUES (1, "Babysicherheit"); 
INSERT INTO taxons (parent_id, name) VALUES (2, "Babysicherungen & Schutzvorrichtungen");

Query #1

EXPLAIN SELECT * 
FROM taxons
WHERE name = "Babysicherungen & Schutzvorrichtungen" 
AND parent_id = (
  SELECT id 
  FROM taxons 
  WHERE name  = "Babysicherheit"
  AND parent_id = (
    SELECT id 
    FROM taxons 
    WHERE name = "Baby & Kleinkind"
  )
);

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | --- | ----------- | ------ | ---------- | ---- | ------------- | --- | ------- | --- |

---- | -------- | ----------- |
| 1   | PRIMARY     | taxons |            | ALL  |               |     |         |     | 3    | 33.33    | Using where |
| 2   | SUBQUERY    | taxons |            | ALL  |               |     |         |     | 3    | 33.33    | Using where |
| 3   | SUBQUERY    | taxons |            | ALL  |               |     |         |     | 3    | 33.33    | Using where |

---

View on DB Fiddle

Schema (MySQL v5.7)

CREATE TABLE taxons (
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    parent_id int,
    name varchar(255)
);

INSERT INTO taxons (parent_id, name) VALUES (NULL, "Baby & Kleinkind"); 
INSERT INTO taxons (parent_id, name) VALUES (1, "Babysicherheit"); 
INSERT INTO taxons (parent_id, name) VALUES (2, "Babysicherungen & Schutzvorrichtungen");

Query #1

EXPLAIN select t1.* from taxons t1
join taxons t2 on t1.name = "Babysicherungen & Schutzvorrichtungen" and t1.parent_id = t2.id and t2.name = "Babysicherheit"
join taxons t3 on t2.parent_id = t3.id and t3.name="Baby & Kleinkind"
;

| id  | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra       |
| --- | ----------- | ----- | ---------- | ------ | ------------- | ------- | ------- | ----------------- | ---- | -------- | ----------- |
| 1   | SIMPLE      | t1    |            | ALL    |               |         |         |                   | 3    | 33.33    | Using where |
| 1   | SIMPLE      | t2    |            | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.parent_id | 1    | 33.33    | Using where |
| 1   | SIMPLE      | t3    |            | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.parent_id | 1    | 33.33    | Using where |

View on DB Fiddle

Great! Didn't thought about joins! – Enthusiasmus Jul 24 '19 at 12:07 — Enthusiasmus, Jul 24 '19 at 12:07

krock · Accepted Answer · 2019-07-22T17:30:37.600

If I'm reading your query correctly, you want to query the top level records, but only those records where all the child rows exist as well. For example, if "Baby & Kleinkind" doesn't exist for some ID, then we don't want that ID.

If so, you could use dynamic SQL to build a query that automatically checks every parent-child relationship for you. If you have a lot of taxons, this could be save you time.

The idea is to build a query that looks like this (assuming that there are five taxons):

SELECT t1.* 
FROM taxons t1
INNER JOIN taxons t2
  ON t1.id = t2.parent_id 
INNER JOIN taxons t3
  ON t2.id = t3.parent_id
INNER JOIN taxons t4
  ON t3.id = t4.parent_id
INNER JOIN taxons t5
  ON t4.id = t5.parent_id
WHERE NAME = "Babysicherungen & Schutzvorrichtungen"

To build that, you can use a loop:

DECLARE totalLevels INT;
DECLARE num INT;
DECLARE queryString VARCHAR(255);

SET @totalLevels = 5
SET @num = 2;
SET @str = 'SELECT t1.* 
FROM taxons t1';

build_query: LOOP
  IF @num > @totalLevels THEN
    LEAVE build_query;
  END IF;
  SET @queryString = CONCAT(@queryString,
    ' INNER JOIN taxons t', num, ' ON t', num-1, '.id = t', num, '.parent_id'
  );
  SET @num = @num + 1;
  ITERATE build_query;
END LOOP;

SET @queryString = CONCAT(@queryString, ' WHERE NAME = "Babysicherungen & Schutzvorrichtungen"')

You use totalLevels to set the total number of taxons that you want to traverse.

I haven't specifically tested the code in that loop, but I've done similar queries in the past, and it should work, or only require minor changes to work.

EDIT: I forgot to add, once you create the query string, you need to execute it. For that, see here: Is it possible to execute a string in MySQL?

Simplify recursive mysql query

2 Answers2