Hey guys,
I have a query that's giving me abysmal performance and it's not immediately
obvious to me as to what's wrong with the table structure to cause this.
CREATE TABLE profile (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL
);
CREATE INDEX profile_idx_0 ON profile (
id,
name
);
CREATE TABLE p_attribute (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pid INTEGER NOT NULL
REFERENCES profile (id)
ON DELETE CASCADE,
aid INTEGER NOT NULL
REFERENCES attribute (id)
ON DELETE CASCADE,
value TEXT
);
CREATE INDEX p_attribute_idx_0 ON p_attribute (
id,
pid,
aid
);
CREATE TABLE attribute (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
CHECK(UPPER(name) = name)
);
CREATE INDEX attribute_idx_0 ON attribute (
id,
name
);
SELECT a.name, p.value
FROM p_attribute p
JOIN attribute a
ON a.id=p.aid
WHERE p.pid=?
This returns all relevant rows I need, where table profile has ~6000 rows,
p_attribute has ~ 170k and attribute has ~60 rows.
Analyze has been run, explain query plan shows:
recno selected order from detail
0 0 0 SCAN TABLE p_attribute AS p
0 1 1 SEARCH TABLE attribute AS a USING INTEGER PRIMARY KEY
(rowid=?)
Any pointers as to what may not be optimal?
Thanks,
jlc
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
