Have you tried running ANALYZE on a representative dataset? This will determine the "shape" of your tables and allow the query planner to make better guesses of the costs associated with each join.
If, after ANALYZE, you still feel the need to improve over the query planners' ordering, you can still use CROSS JOIN to force a certain order of tables. -----Urspr?ngliche Nachricht----- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Yuri Gesendet: Mittwoch, 20. J?nner 2016 18:14 An: General Discussion of SQLite Database Betreff: [sqlite] SQLite scans the largest table in the query where a very small table is available I have the select query over the linked chain of tables with all needed indices present. All but one tables (m,h,e,w) have row count ~300,000-700,000, mt table has only ~150 rows. Obviously, based on the row count, scan should begin from the table mt. However, SQLite actually scans beginning from m, and the query returning only ~150 rows runs for 2+ seconds. Does SQLite optimizer look at the row counts? (I think it does because the plans are different when there are no rows present). How to correct the problem? ---schema.sql--- CREATE TABLE w (w_id INTEGER PRIMARY KEY AUTOINCREMENT, t TEXT NOT NULL UNIQUE); CREATE TABLE e (e_id INTEGER PRIMARY KEY AUTOINCREMENT, w_id INTEGER NOT NULL); CREATE TABLE h (h_id INTEGER PRIMARY KEY AUTOINCREMENT, e_id INTEGER NOT NULL, FOREIGN KEY(e_id) REFERENCES e(e_id)); CREATE TABLE m (m_id INTEGER PRIMARY KEY AUTOINCREMENT, h_id INTEGER NOT NULL, FOREIGN KEY(h_id) REFERENCES h(h_id)); CREATE TABLE mt (m_id INTEGER PRIMARY KEY, FOREIGN KEY(m_id) REFERENCES m(m_id)); ---query.sql--- select h.h_id, w.t from w w, e e, h h, m m, mt mt where e.w_id = w.w_id and h.e_id = e.e_id and m.h_id = h.h_id and mt.m_id = m.m_id group by e.w_id; Plan goes like this: 0|0|3|SCAN TABLE m AS m USING COVERING INDEX m_h_index 0|1|2|SEARCH TABLE h AS h USING INTEGER PRIMARY KEY (rowid=?) .... Yuri _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
