We are tracking manufactured components with an alphanumeric serial
number, which gives us a natural key. Naturally we set type affinity
TEXT on the key column. There are many tables linked through foreign key
relationships on this serial number.
On one table, the type affinity of the serial number FOREIGN KEY column
was incorrectly declared as INTEGER. Due to the flexibility of the type
affinity system, this never showed up as an issue. We were able to
INSERT valid TEXT values into the column with no problem. Everything
seemed to be working fine.
As the database grew modestly, some queries became very slow. After
making sure that all relevant columns were already automatically indexed
due to FOREIGN KEY and PRIMARY KEY declarations, I found the culprit.
After changing the type affinity on the bad table from INTEGER to TEXT,
the slow query which joined on that table sped up by two orders of
magnitude.
I'm happy enough with the end result, but I'd quite like to know why
this happened? Was SQLite building a different type of index under the
hood due to the declared type affinity? Did the difference in type
affinity of the compared columns prevent the query from using the index?
Was there some unnecessary type conversion going on which slowed things
down?
Here is an cut down example:
CREATE TABLE IF NOT EXISTS pcb_units (
serial_no TEXT NOT NULL,
PRIMARY KEY (serial_no),
-- Constraints enforcing serial number validity.
CONSTRAINT family_check
CHECK (SUBSTR(serial_no, 6, 1) BETWEEN 'A' AND 'Z' OR
SUBSTR(serial_no, 6, 1) BETWEEN '0' AND '9'),
CONSTRAINT model_check
CHECK (SUBSTR(serial_no, 7, 1) BETWEEN 'A' AND 'Z' OR
SUBSTR(serial_no, 7, 1) BETWEEN '0' AND '9'),
CONSTRAINT reserved_check
CHECK (SUBSTR(serial_no, 8, 1) == '0'),
CONSTRAINT unit_number_check
CHECK (CAST(SUBSTR(serial_no, 9, 4) AS INTEGER) BETWEEN 0 AND 9999)
);
CREATE TABLE assembly_pcb_units (
pcb_serial_no INTEGER NOT NULL, -- This is the bad column
assembly_serial_no TEXT NOT NULL,
added_time TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
added_by TEXT NOT NULL,
CONSTRAINT pcb_in_one_assembly_only
UNIQUE(pcb_serial_no),
FOREIGN KEY (assembly_serial_no)
REFERENCES assembly_units(assembly_serial_no),
FOREIGN KEY (pcb_serial_no)
REFERENCES pcb_units(serial_no),
FOREIGN KEY (added_by)
REFERENCES sti_users(user_id)
);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
