On Wed, 17 Jul 2013 12:04:52 +0200 Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> wrote:
> On Tue, Jul 16, 2013 at 8:13 PM, Eduardo <emorr...@yahoo.es> wrote: > > > Can you show us the query and/or schemas? If not: > Sure, I appended everything in the bottom of this email. > Unfortunately gmail will mess-up the layout, I hope it will be > readable. > > (See here, it seems google does not know the mean of "should") > https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04 > > > a) Does it JOIN multiple big tables (in rows and/or columns)? > No, but I got a sub query is probably the culprit. > > > e) Did you normalize the database? > Should be, yes. > > > In both cases (if you can show us the query/schema or not) what do you > > really want to ask to the database? (not to us but to sqlite3, perhaps the > > query can be reformulated) > > > Here is everything... I think the problem is shown in the: > Explain: 0 0 0 SCAN TABLE tour AS t1 (~1000000 rows) > that is part of a sub-query. > > > -- First statement (sets the pragmas): > > PRAGMA foreign_keys = ON; > PRAGMA synchronous = OFF; > PRAGMA journal_mode = MEMORY; > PRAGMA cache_size = -10240; > PRAGMA auto_vacuum = NONE; > PRAGMA locking_mode = EXCLUSIVE; > PRAGMA secure_delete = OFF; > PRAGMA temp_store = 0; > > -- Second statement (creates/clean the schema): > BEGIN TRANSACTION; > DROP TABLE IF EXISTS tour; > CREATE TABLE tour (id integer, > score integer NOT NULL, > cost integer NOT NULL, > last_poi integer, > unsorted_path blob, > unsorted_path_tmp blob, > PRIMARY KEY(id)); > 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: 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. You use '==' instead '=', take care too. I made the same error in a mail some weeks ago. 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. HTH --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
