Tristan Van Berkom wrote: > When using an INNER join, the engine does something like this: > > o Create a data set that is table_1 * table_2 * table_3 rows > large > > o Run the constraints on what might be multiple matching rows > in the resulting huge data set (even if I nest the selects, > there can be other constraints to sort out on the main table).
This is wrong; constraints on the outer table are checked before records from the inner table are searched. > This bug comment has a good detailed description of the reason > why we shifted from regular joins to LEFT OUTER joins: > https://bugzilla.gnome.org/show_bug.cgi?id=699597#c6 That query was slow because it did not do any join to begin with, ,not even with "a.id=b.id" in the WHERE clause; instead, lots of constraints were combined with OR. > If I were to create indexes on the uid column of the auxiliary > tables, would that cause the INNER join to not create such a > huge dataset before checking the constraints ? I might or might not make a difference; check with EXPLAIN QUERY PLAN. >> WHERE phone_list.value LIKE '%0505' In theory, you could enable index usage by using: WHERE phone_list.value_reversed LIKE '5050%' Not sure if this would be worth the effort. >> Normally, you need case-sensitive LIKE in order to use the index, unless >> the index is created with COLLATE NOCASE. Also, the column must have text affinity. > LIKE is case insensitive by default and we override that indeed, using > "PRAGMA case_sensitive_like=ON" at initialization time. To avoid that, you could use "GLOB 'foo*'" instead of "LIKE 'foo%'". Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
