sqlite3 - SQLite very slow SELECT time -

sqlite3 - SQLite very slow SELECT time -

i got unusual behavior of program, , maybe can bring lite it.

today started testing code, , realized, specific query slow (took 2 minutes).

here select:

select distinct table1.somename table1 inner bring together table2 on table2.id = table1.t2_id inner bring together table3 on table1.id = table3.t1_id inner bring together table4 on table3.id = table4.t3_id inner bring together table5 on table5.id = table4.t5_id inner bring together table6 on table4.id = table6.t4_id t4_name = 'whatever' , t2_name = 'moarwhatever' , timestamp_till null order somename

so thing is, result 120 records. inner joins cut down amount of checks timestamp_till null 20 records on each record.

what bugs me is, i've tested insert whole table table6 new created table , renamed timestamp_till ende. on table select done in 0.1 seconds ...

is timestamp_till sort of reserved name of sqlite3? bug in sqlite engine? fault? oo

edit: add together explain query plan output...

when querying and timestamp_till null gives:

0|0|4|search table table5 using covering index sqlite_autoindex_table5 (t4_name=?) (~1 rows) 0|1|3|search table table4 using index table4.fk_table4_1_idx (t5_id=?) (~10 rows) 0|2|2|search table table3 using integer primary key (rowid=?) (~1 rows) 0|3|0|search table table1 using integer primary key (rowid=?) (~1rows) 0|4|1|search table table2 using integer primary key (rowid=?) (~1 rows) 0|5|5|search table table6 using index table6.fk_table6_ts_till (timestamp_till=?) (~2 rows) 0|0|0|use temp b-tree grouping 0|0|0|use temp b-tree distinct

and fast 1 is:

select distinct table1.somename table1 inner bring together table2 on table2.id = table1.t2_id inner bring together table3 on table1.id = table3.t1_id inner bring together table4 on table3.id = table4.t3_id inner bring together table5 on table5.id = table4.t5_id inner bring together table6 on table4.id = table6.t4_id t4_name = 'whatever' , t2_name = 'moarwhatever' order somename

and result:

0|0|4|search table table5 using covering index sqlite_autoindex_table5_1 (t4name=?) (~1 rows) 0|1|3|search table table4 using index table4.fk_table4_1_idx (t5_id=?) (~10 rows) 0|2|2|search table table3 using integer primary key (rowid=?) (~1 rows) 0|3|0|search table table1 using integer primary key (rowid=?) (~1rows) 0|4|1|search table table2 using integer primary key (rowid=?) (~1 rows) 0|5|5|search table table6 using covering index sqlite_autoindex_table6_1 (id=?) (~10 rows) 0|0|0|use temp b-tree grouping 0|0|0|use temp b-tree distinct

with test-table re-create of table6

0|0|4|search table table5 using covering index sqlite_autoindex_table5_1 (name=?) (~1 rows) 0|1|3|search table table4 using index table4.fk_t5_idx (t5_id=?) (~10 rows) 0|2|2|search table table3 using integer primary key (rowid=?) (~1 rows) 0|3|0|search table table1 using integer primary key (rowid=?) (~1rows) 0|4|1|search table table2 using integer primary key (rowid=?) (~1 rows) 0|5|5|search table test using index test.fk_test__idx (id=?) (~2 rows) 0|0|0|use temp b-tree grouping 0|0|0|use temp b-tree distinct

create script test

create table "test"( "id" integer not null, "t12_id" integer not null, "value" decimal not null, "anfang" integer not null, "ende" integer default null, primary key("id","t12_id","anfang"), constraint "fk_test_t12_id" foreign key("t12_id") references "table12"("id"), constraint "fk_test_id" foreign key("id") references "id_col"("id"), constraint "fk_test_anfang" foreign key("anfang") references "ts_col"("id"), constraint "fk_test_ende" foreign key("ende") references "ts_col"("id") ); create index "test.fk_test_idx_t12_id" on "test"("t12_id"); create index "test.fk_test_idx_id" on "test"("id"); create index "test.fk_test_anfang" on "test"("anfang"); create index "test.fk_test_ende" on "test"("ende");

soo long zai

a first note: sqlite utilize 1 index in query. never more (with current version).

thus, here sqlite doing:

slow query: utilize index on timestamp_till fast query (no timestamp_till): utilize (auto) index on table6.id.

i see 2 workarounds.

you utilize subquery:

select distinct somename ( select table1.somename "somename", timestamp_till table1 inner bring together table2 on table2.id = table1.t2_id inner bring together table3 on table1.id = table3.t1_id inner bring together table4 on table3.id = table4.t3_id inner bring together table5 on table5.id = table4.t5_id inner bring together table6 on table4.id = table6.t4_id t4_name = 'whatever' , t2_name = 'moarwhatever' ) q timestamp_till null order somename;

or can drop index on timestamp_till, if don't need elsewhere.

there perhaps speed gains made reordering joins. smallest table first faster, can vary greatly.

select sqlite3 inner-join