> Some changes, if blob is bigger than a few bytes, you should normalize them. > If 2 blobs are equal, their id must be equal and you don't waste time > comparing nor memory joining blob content. So you get: They are quite small (max ~70 bytes...)
> DROP TABLE IF EXISTS tour_blob; > CREATE TABLE tour_blob (id INTEGER PRIMARY KEY, > n_blob blob); > > DROP TABLE IF EXISTS tour; > CREATE TABLE tour (id integer, > score integer NOT NULL, > cost integer NOT NULL, > last_poi integer, > FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON > DELETE CASACADE, > FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) > ON DELETE CASACADE, > PRIMARY KEY(id)); > > You can replace tour_unsorted_path_idx with a new index too: > >> DROP INDEX IF EXISTS tour_unsorted_path_idx; >> CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path); > > DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx; > CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, > last_poi, cost); > > Take care and make cost the last one, because cost is compared with > inequality. Very interesting, this sounds a very good point! > You use '==' instead '=', take care too. I made the same error in a mail some > weeks ago. Ops... > For this query: > >> >> SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1 >> 8< ----------------- >> Query: SELECT id FROM tour ORDER BY id LIMIT ?1 >> Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~1000000 rows) >> ----------------- >8 > > I don't know why it doesn't use the primary index. Perhaps analyze statistics > before solves the problem. It seems Explain does not consider the LIMIT, it should not be something to worry about. Thanks a lot. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
