On Wed, Mar 5, 2014 at 7:20 PM, Joseph L. Casale <jcas...@activenetwerx.com>wrote:
> 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 > ); > Don't put the PRIMARY KEY as the first column of your index. Ever. This applies to all SQL database engines, not just SQLite. For that matter, don't put the PRIMARY KEY anywhere in your index. The PRIMARY KEY will be added automatically at the end, where it belongs. If you remove the "id," from all of your indices, I think your performance will probably improve dramatically. > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
