storage/db: add NOT NULL and PRIMARY KEY constraints to improve performance
In a test database containing 309,000 real benchmark records, given
the query
select t1.UploadID, (select t2.Value from RecordLabels t2 where
t2.UploadID = t1.UploadID AND Name = 'upload-time' limit 1) from
Uploads t1;
used to obtain sample values from each upload, the PRIMARY KEY index
causes the query to fall from 1.49s to 0.03s.
The NOT NULL constraint is necessary to make the PRIMARY KEY effective.
Change-Id: I9e663766e2f99f1096952e4c20eecbf8faab577b
Reviewed-on: https://go-review.googlesource.com/36114
Reviewed-by: Russ Cox <rsc@golang.org>
diff --git a/storage/db/db.go b/storage/db/db.go
index 6794847..5c03b4f 100644
--- a/storage/db/db.go
+++ b/storage/db/db.go
@@ -86,21 +86,22 @@
CREATE INDEX IF NOT EXISTS UploadDaySeq ON Uploads(Day, Seq);
{{end}}
CREATE TABLE IF NOT EXISTS Records (
- UploadID VARCHAR(20),
- RecordID BIGINT UNSIGNED,
- Content BLOB,
+ UploadID VARCHAR(20) NOT NULL,
+ RecordID BIGINT UNSIGNED NOT NULL,
+ Content BLOB NOT NULL,
PRIMARY KEY (UploadID, RecordID),
FOREIGN KEY (UploadID) REFERENCES Uploads(UploadID) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS RecordLabels (
- UploadID VARCHAR(20),
- RecordID BIGINT UNSIGNED,
- Name VARCHAR(255),
- Value VARCHAR(8192),
+ UploadID VARCHAR(20) NOT NULL,
+ RecordID BIGINT UNSIGNED NOT NULL,
+ Name VARCHAR(255) NOT NULL,
+ Value VARCHAR(8192) NOT NULL,
{{if not .sqlite3}}
Index (Name(100), Value(100)),
{{end}}
- FOREIGN KEY (UploadID, RecordID) REFERENCES Records(UploadID, RecordID) ON UPDATE CASCADE ON DELETE CASCADE
+ PRIMARY KEY (UploadID, RecordID, Name),
+ FOREIGN KEY (UploadID, RecordID) REFERENCES Records(UploadID, RecordID) ON UPDATE CASCADE ON DELETE CASCADE
);
{{if .sqlite3}}
CREATE INDEX IF NOT EXISTS RecordLabelsNameValue ON RecordLabels(Name, Value);
