On 6 Mar 2014, at 12:20am, Joseph L. Casale <jcas...@activenetwerx.com> wrote:
> 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? You don't have ideal indexes. The main task of this SELECT is to look for certain values of p_attribute.pid. You don't have an index that makes this easy. Do something like CREATE INDEX p_attribute_idx_0 ON p_attribute (pid, id) Once you've done this do an ANALYZE just to make sure, then try the SELECT again. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
