CODEWITHSUNDEEP
sqlsqlite
Calebmer
Calebmer

Reputation: 2860

How does SQLite behave when inserting unordered hashes as the rowid?

According to the SQLite documentation on rowid the data for rowid tables is stored in a B-tree. I’ve been considering using a hash of my data as the rowid. Since this means I’d be inserting rows with rowids that are not ordered like the default implementation of rowid how will this impact INSERT and SELECT performance in addition to the layout of data in my table?

If I insert a row which has a large rowid because it’s a hash and then a row with a smaller rowid what will the table layout look like?

Upvotes: 0

Views: 72

Answers (1)

MikeT
MikeT

Reputation: 56948

It would depend upon how.

If you do not define an alias for the rowid column and a VACUUM takes place then the rowid values will likely be messed up (as they may/will be re-assigned).

e.g. :-

DROP TABLE IF EXISTS tablex;
CREATE TABLE IF NOT EXISTS tablex (data TEXT);
INSERT INTO tablex (rowid,data) VALUES(82356476978,'fred'),(55,'mary');
SELECT rowid AS therowid,* FROM tablex;
VACUUM;
SELECT rowid AS therowid,* FROM tablex;

results in :-

enter image description here

and then :-

enter image description here

If an alias is defined the VACUUM shouldn't be an issue and as above, it's fine to do so.

Of course you have to adhere to the rules and as long as the rules are obeyed, that is that the values are unique integers and are not greater than 9223372036854775807 or less than -9223372036854775808, then it should be fine. Other values would result in a datatype mismatch error.

I don't believe there would be much of an impact upon performance, there could possibly even be an improvement as there may well be space free in leaves reducing the need for a more costly split.

e.g. the following :-

DROP TABLE IF EXISTS tabley;
CREATE TABLE IF NOT EXISTS tabley (myrowidalias INTEGER PRIMARY KEY ,data TEXT);
INSERT INTO tabley VALUES(9223372036854775807,'fred'),(-9223372036854775808,'Mary'),(55,'Sue');
SELECT rowid AS therowid,* FROM tabley;
VACUUM;
SELECT rowid AS therowid,* FROM tabley;

-- INSERT INTO tabley VALUES(9223372036854775808,'Sarah'); -- Dataype mismatch
INSERT INTO tabley VALUES(-9223372036854775809,'Bob'); -- Datatype mismatch
SELECT rowid AS therowid,* FROM tabley; -- not run due to above error

Results in (note rowid retrieved via rowid and it's alias) :-

enter image description here

and after the VACUUM (identical) :-

enter image description here

With message :-

-- INSERT INTO tabley VALUES(9223372036854775808,'Sarah');
INSERT INTO tabley VALUES(-9223372036854775809,'Bob')
> datatype mismatch
> Time: 0s

Upvotes: 1

Related Questions