[Solved] Deleting nodes in a database tree

EverSQL Database Performance Knowledge Base

Deleting nodes in a database tree

Is it possible to simplify or improve how I delete a selected node together with all subnodes below?

Table looks like this:

CREATE TABLE cats (cat_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
                   cat_pid INTEGER NOT NULL,
                   cat_name VARCHAR(40)
                  )

The query:

WITH tree (cat_id, cat_pid, level, cat_name) AS
(
  SELECT cat_id, cat_pid, 0 as level, cat_name
  FROM cats
  WHERE cat_pid = :cat_id --parameter from FreePascal/Lazarus

  UNION ALL

  SELECT cats.cat_id, cats.cat_pid, tree.level + 1, cats.cat_name
  FROM cats
  INNER JOIN tree ON tree.cat_id = cats.cat_pid
)
DELETE FROM cats
 WHERE cat_id IN (SELECT cat_id FROM tree)
    OR cat_id=:cat_id

If possible, I would like to get rid of the OR in the DELETE statement, but dont know how.

Actually, if deletes could be done using foreign keys/cascading deletes it would also be very interesting.

The database is SQLite.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `cats` ADD INDEX `cats_idx_cat_pid` (`cat_pid`);
The optimized query:
WITH tree (cat_id, cat_pid, level, cat_name) AS (SELECT
        cats.cat_id,
        cats.cat_pid,
        0 AS level,
        cats.cat_name 
    FROM
        cats 
    WHERE
        cats.cat_pid = :cat_id 
    UNION
    ALL SELECT
        cats.cat_id,
        cats.cat_pid,
        tree.level + 1,
        cats.cat_name 
    FROM
        cats 
    INNER JOIN
        tree 
            ON tree.cat_id = cats.cat_pid) SELECT
            1 
    FROM
        cats 
    WHERE
        cats.cat_id IN (
            SELECT
                tree.cat_id 
            FROM
                tree
        ) 
        OR cats.cat_id = :cat_id

Related Articles



* original question posted on StackOverflow here.