MariaDB

MariaDB is the community fork of the server-based MySQL database system. MariaDB supports Recursive Common Table Expressions since version 10.2.2.

To measure the resource usage for the MariaDB server systems we took a snapshot of the status information provided by at the start of the client program and when the query was finished. The difference between both snapshots is used to measure CPU time. This approach can not be used to measure memory usage. We made sure that for MariaDB only one user was connected, as we gathered the status information of the server process.

tcff

We've added an index on the second column of the par relation.

-- tcff.sql.[innodb/memory].tbl
CREATE DATABASE IF NOT EXISTS tc;
use tc;

SET max_heap_table_size = 1024*1024*1024*4; -- only for memory

DROP TABLE IF EXISTS par;
CREATE TABLE par (
  a INT NOT NULL,
  b INT NOT NULL,
  CONSTRAINT par_pk PRIMARY KEY (a, b)
)
ENGINE = Memory; -- or Innodb
CREATE INDEX par_fb ON par (b); --BTREE or HASH for MEMORY

LOAD DATA LOCAL INFILE '{instance}'
INTO TABLE par
COLUMNS TERMINATED BY ', ';
-- tcff.sql
WITH RECURSIVE tc(a,b) AS (
  SELECT par.a, par.b from par
  UNION
  SELECT par.a, tc.b from par JOIN tc ON par.b = tc.a
) SELECT Count(*) FROM tc;
-- tcff.sql.drop.tbl
use tc;
DROP TABLE par;

Launch: cat tcff.sql.[innodb/memory].tbl tcff.sql tcff.sql.drop.tbl | mysql --verbose --user=root -B

sgff

We're using the same table definitions as for the transitive closure.

-- sgff.sql
WITH RECURSIVE sg(a,b) AS (
    SELECT par.b, par.b from par
    UNION
    SELECT p1.a, p2.a from sg JOIN par p1 ON p1.b = sg.a JOIN par p2 ON p2.b = sg.b
) SELECT Count(*) FROM sg;

Launch: cat tcff.sql.[innodb/memory].tbl sgff.sql tcff.sql.drop.tbl | mysql --verbose --user=root -B