On Thu, 2013-11-28 at 09:43 +0100, Clemens Ladisch wrote:
> 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.
Yes I will have to do more research, we'll see.
>
> >> 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.
It is, and we store data in reverse to speed up suffix
searches in some cases (if configured to do so).
>
> >> 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%'".
However I found the escape sequences to be more easy with LIKE
statements, so better not to avoid that.
Again, while I appreciate the comments about how we think the
best way SQLite should work in the most optimal way, does anyone
have an answer to the question ?
Are the JOIN statements equal to the logical AND statements,
for all practical purposes ?
Best Regards,
-Tristan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
