Foreign keys revisited

General
#1

i asked about foreign keys with cascase delete some time ago.

so with 2.0 i am revisiting this topic. here is my table schema:

CREATE TABLE alternateParent
( id INT PRIMARY KEY
, alternateParent_value VARCHAR(50) NOT NULL
);

CREATE TABLE grandParentTable
( id INT PRIMARY KEY
, grandParentTable_name VARCHAR(50) NOT NULL
);

CREATE TABLE parentTable
( id INT PRIMARY KEY
, grandParentTable INT NOT NULL REFERENCES grandParentTable(id) ON DELETE CASCADE ON UPDATE CASCADE
, parentTable_name VARCHAR(500) NOT NULL
, INDEX (grandParentTable)
);

CREATE TABLE childTable
( id INT PRIMARY KEY
, parentTable INT NOT NULL REFERENCES parentTable (id) ON DELETE CASCADE ON UPDATE CASCADE
, alternateParent INT NOT NULL REFERENCES alternateParent (id) ON DELETE CASCADE ON UPDATE CASCADE
, childTable_name VARCHAR(500) NOT NULL
, INDEX (parentTable )
, index (alternateParent)
);

each table has 10 more rows than the one “above” it, so the lowest table (childTable) has 1000 rows.
so grandparent has 10, parent has 100, and child has 1000.

here is my query:

SELECT count(*)
FROM grandParentTable, parentTable, alternateParent, childTable
WHERE 1=1
AND (alternateParent.id      = childtable.alternateParent )
AND (parenttable.id            = childtable.parenttable)
AND (grandParentTable.id  = parentTable.grandParentTable)
AND (parentTable.id = 5)

my initial results are “bat outta hell” speeds, compared to earlier versions of cockroachDB.

my question: is this a “fair” test to test a grandparent-parent-child relationship? these speeds appear to blow mysql out of the water.

assuming this is a fair test, congratulations on such a successful release!

and yes, CASCADE DELETE is working wonderfully too.

#2

Hi Edward,

I do not know whether this is a fair test, but congratulations on your success.

Cheers for keeping us updated on this!

#3

fwiw, i loaded up the tables as follows:

grandparenttable: 100 rows
parenttable: 5,000 rows
alternateparent: 10,000 rows
childtable: 100,000

and fired off a few queries that looked like this:

SELECT *
FROM grandParentTable, parentTable, alternateParent, childTable
WHERE 1=1
AND (alternateParent.id = childtable.alternateParent )
AND (parenttable.id = childtable.parenttable)
AND (grandParentTable.id = parentTable.grandParentTable)
AND (parentTable.id = 123 ) and childtable.alternateparent in ( 24500, 24600, 24700 );

the speed results are nothing short of phenomenal. its somewhere between “greased lightning” and “bat-outta-hell” speeds.

i get the impressionfrom my earlier post that using the previously dreaded Join is possible and now very acceptable. These results seem to leave 1.x results in the dust, along with many other popular names.

#4

i tried it again on 1.1.4, and the speed is about the same.

looking back, i originally started all these threads since that nasty JOIN was overloading cockroach a couple of years ago. but either way, this is a victory since the original issue clearly has been resolved.

and with the new ON DELETE CASCASE ON UPDATE CASCADE, this is a green-light go-ahead signal for us to move to cockroachDB.

onward and upward! next time i see a living cockroach, i might pause for a second before squishing it.