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.
Von: [hidden email] [mailto:[hidden email]] 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?
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));
e.w_id = w.w_id
h.e_id = e.e_id
m.h_id = h.h_id
mt.m_id = m.m_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=?)
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]
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.
On 01/20/2016 09:21, Hick Gunter wrote:
> ave 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.
ANALYZE helped, thanks!