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
