From dcf66ebe06a08534e765f198d2c07ce7d85fe72b Mon Sep 17 00:00:00 2001 From: Tatiana Racheva Date: Fri, 28 Sep 2018 11:02:09 -0700 Subject: [PATCH] Define DEPTABLE's KEY_VERTEX as INTEGER PRIMARY KEY Summary: This very small change decreases the size of the SQLite table by ~24% and improves the writing time in my very unscientific test by about 16%. In SQLITE, INT is not the same as INTEGER. A table without an INTEGER PRIMARY KEY is created with a ROWID column. Specifying INTEGER PRIMARY KEY aliases ROWID to the user-created column. Additionally, [according to the SQLite documentation](https://www.sqlite.org/lang_createtable.html): > Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. At this time, this isn't terribly useful, because, as Jake pointed out to me, the cost of lookups is dwarved compared to the cost of reparsing and rechecking we do when looking up dependencies. I tested `--find-refs EntSchemaBase`, and the results were identical between the two states. I suspect we defined the primary key as INT for DEPTABLE because INTEGER PRIMARY KEY used to be a 32-bit int and our KEY_VERTEX values were 64-bit. Our other tables define the primary key as INTEGER PRIMARY KEY already. The ROWID column is now also a 64-bit integer, so this change is safe. Again, [from the documentation](https://www.sqlite.org/autoinc.html): > In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer Reviewed By: ljw1004 Differential Revision: D10097554 fbshipit-source-id: d24fc7f64f03984520d1aa3ae9165918a2d3d006 --- hphp/hack/src/heap/hh_shared_sqlite.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/hphp/hack/src/heap/hh_shared_sqlite.c b/hphp/hack/src/heap/hh_shared_sqlite.c index 935a6684d09..d68113ec915 100644 --- a/hphp/hack/src/heap/hh_shared_sqlite.c +++ b/hphp/hack/src/heap/hh_shared_sqlite.c @@ -46,7 +46,7 @@ const char *create_tables_sql[] = { " FILESPEC TEXT NOT NULL" \ ");", "CREATE TABLE IF NOT EXISTS DEPTABLE(" \ - " KEY_VERTEX INT PRIMARY KEY NOT NULL," \ + " KEY_VERTEX INTEGER PRIMARY KEY NOT NULL," \ " VALUE_VERTEX BLOB NOT NULL" \ ");", }; -- 2.11.4.GIT