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)); DROP INDEX IF EXISTS tour_unsorted_path_idx; CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path); DROP TABLE IF EXISTS categories; CREATE TABLE categories (tour_id integer NOT NULL, order_idx integer NOT NULL, value integer NOT NULL, FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE); DROP INDEX IF EXISTS cats_tour_id_idx; CREATE INDEX cats_tour_id_idx ON categories (tour_id); DROP TABLE IF EXISTS path; CREATE TABLE path (tour_id integer NOT NULL, order_idx integer NOT NULL, node_id integer NOT NULL, FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE); DROP INDEX IF EXISTS path_tour_id_idx; CREATE INDEX path_tour_id_idx ON path (tour_id); DROP TRIGGER IF EXISTS set_last_poi_trg; CREATE TRIGGER set_last_poi_trg AFTER INSERT ON path BEGIN UPDATE tour SET last_poi = ( SELECT node_id FROM path WHERE tour_id == NEW.tour_id ORDER BY order_idx DESC LIMIT 1 ) WHERE id == NEW.tour_id; END; COMMIT; VACUUM; -- Finally the statement executed in a normal program -- execution with the explain if appliable. -- I prepare all this statements and put in hash table -- and finalize them at the end of the program. -- This output comes from the first time, when I store them. SQL STATEMENT: INSERT INTO path (tour_id, order_idx, node_id) VALUES (?1, ?2, ?3) SQL STATEMENT: INSERT INTO tour (score, cost) VALUES (?1, ?2) SQL STATEMENT: UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2 8< ----------------- Query: UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2 Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) ----------------- >8 SQL STATEMENT: UPDATE tour SET unsorted_path=unsorted_path_tmp, unsorted_path_tmp = NULL WHERE id = ?1; 8< ----------------- Query: UPDATE tour SET unsorted_path=unsorted_path_tmp, unsorted_path_tmp = NULL WHERE id = ?1; Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) ----------------- >8 SQL STATEMENT: INSERT INTO categories (tour_id, order_idx, value) VALUES (?1, ?2, ?3) SQL STATEMENT: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) 8< ----------------- Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~25 rows) ----------------- >8 8< ----------------- Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) Explain: 0 0 0 EXECUTE LIST SUBQUERY 0 ----------------- >8 8< ----------------- Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) Explain: 0 0 0 SCAN TABLE tour AS t1 (~1000000 rows) ----------------- >8 8< ----------------- Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) Explain: 0 1 1 SEARCH TABLE tour AS t2 USING INDEX tour_unsorted_path_idx (unsorted_path=?) (~2 rows) ----------------- >8 8< ----------------- Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) Explain: 0 0 0 SEARCH TABLE path USING COVERING INDEX path_tour_id_idx (tour_id=?) (~10 rows) ----------------- >8 8< ----------------- Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost < t2.cost) Explain: 0 0 0 SEARCH TABLE categories USING COVERING INDEX cats_tour_id_idx (tour_id=?) (~10 rows) ----------------- >8 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 SQL STATEMENT: DELETE FROM tour WHERE id=?1 8< ----------------- Query: DELETE FROM tour WHERE id=?1 Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) ----------------- >8 8< ----------------- Query: DELETE FROM tour WHERE id=?1 Explain: 0 0 0 SEARCH TABLE path USING COVERING INDEX path_tour_id_idx (tour_id=?) (~10 rows) ----------------- >8 8< ----------------- Query: DELETE FROM tour WHERE id=?1 Explain: 0 0 0 SEARCH TABLE categories USING COVERING INDEX cats_tour_id_idx (tour_id=?) (~10 rows) ----------------- >8 SQL STATEMENT: SELECT score,cost,unsorted_path FROM tour WHERE id == ?1 8< ----------------- Query: SELECT score,cost,unsorted_path FROM tour WHERE id == ?1 Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) ----------------- >8 SQL STATEMENT: SELECT node_id FROM path WHERE tour_id == ?1 ORDER BY order_idx 8< ----------------- Query: SELECT node_id FROM path WHERE tour_id == ?1 ORDER BY order_idx Explain: 0 0 0 SEARCH TABLE path USING INDEX path_tour_id_idx (tour_id=?) (~10 rows) ----------------- >8 8< ----------------- Query: SELECT node_id FROM path WHERE tour_id == ?1 ORDER BY order_idx Explain: 0 0 0 USE TEMP B-TREE FOR ORDER BY ----------------- >8 SQL STATEMENT: SELECT value FROM categories WHERE tour_id == ?1 ORDER BY order_idx 8< ----------------- Query: SELECT value FROM categories WHERE tour_id == ?1 ORDER BY order_idx Explain: 0 0 0 SEARCH TABLE categories USING INDEX cats_tour_id_idx (tour_id=?) (~10 rows) ----------------- >8 8< ----------------- Query: SELECT value FROM categories WHERE tour_id == ?1 ORDER BY order_idx Explain: 0 0 0 USE TEMP B-TREE FOR ORDER BY ----------------- >8 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
