On Fri, 2014-09-12 at 15:35 -0600, Keith Medcalf wrote:
> What happens if you phrase it like this?
>
>SELECT DISTINCT summary.uid, summary.vcard
> FROM folder_id AS summary
> LEFT JOIN 'folder_id_email_list' AS email_list
>ON email_list.uid = summary.uid
> AND email_list.va
What happens if you phrase it like this?
SELECT DISTINCT summary.uid, summary.vcard
FROM folder_id AS summary
LEFT JOIN 'folder_id_email_list' AS email_list
ON email_list.uid = summary.uid
AND email_list.value like 'foo%'
WHERE email_list.value like 'foo%'
OR summ
On Wed, 27 Nov 2013 at 21:21:43 -0800, Igor Tandetnik wrote
> Why are you using outer joins when your WHERE clause discards
> unmatched records anyway? If you replace LEFT OUTER with INNER, the
> end result would be exactly the same.
Not for all queries.
Consider the query
(or (beginswith "full
On 11/30/2013 11:51 PM, Tristan Van Berkom wrote:
Perhaps there is a definite and clear way to find the highest possible
character ?
U+10, in a Unicode encoding of your choice.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.o
On 11/30/2013 7:40 PM, Simon Slavin wrote:
Don't use LIKE or GLOB for prefix matches. Although you as a human can tell
that
email_list.value LIKE 'eddie%'
is a prefix match, all the computer sees is pattern-matching.
False. See http://sqlite.org/optoverview.html#like_opt
--
Igor Tandetnik
On 1 Dec 2013, at 4:51am, Tristan Van Berkom wrote:
> Do you have a suggestion which does not make an assumption about what
> is the highest value of a character ?
No. The trick is common in many computer languages and I've never seen a good
formulaic way of doing that. You generally see tha
On Sun, 2013-12-01 at 00:40 +, Simon Slavin wrote:
> On 30 Nov 2013, at 5:40pm, Tristan Van Berkom wrote:
>
> > So, is there a way that I can tell SQLite forcibly to
> > prioritize the index on email_list.value when making
> > a prefix match ?
>
> Don't use LIKE or GLOB for prefix matches.
On 30 Nov 2013, at 5:40pm, Tristan Van Berkom wrote:
> So, is there a way that I can tell SQLite forcibly to
> prioritize the index on email_list.value when making
> a prefix match ?
Don't use LIKE or GLOB for prefix matches. Although you as a human can tell
that
>>> email_list.value LIKE 'e
Tristan,
My apologies to you and the list if you mentioned this earlier, but I
assume you've run the analyze command on your database, right?
http://www.sqlite.org/lang_analyze.html
Also possibly relevant: http://www.sqlite.org/compile.html#enable_stat3
(Of course, Igor's suggestion of the uni
On 11/30/2013 12:40 PM, Tristan Van Berkom wrote:
However, in a statement formed like the one you proposed
above, it screws with the query optimizer in SQLite I suspect,
i.e. when searching for a prefix on an email address,
SQLite (I suspect) decides to prioritize the UID index instead
of the per
On Thu, 2013-11-28 at 00:20 -0500, Igor Tandetnik wrote:
> On 11/27/2013 11:52 PM, Tristan Van Berkom wrote:
> >
> > SELECT DISTINCT summary.uid, summary.vcard FROM 'folder_id' AS summary
> > LEFT OUTER JOIN 'folder_id_phone_list' AS phone_list
> >
On 11/28/2013 2:40 AM, Tristan Van Berkom wrote:
I.e. is the statement logically the same ?
Yes, I do believe that the two queries shown in your original post are
logically equivalent.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sq
On Thu, 2013-11-28 at 12:19 +, Simon Slavin wrote:
> On 28 Nov 2013, at 11:22am, Tristan Van Berkom
> wrote:
>
> > Yes, I definitely agree that on a conceptual level, I should not
> > have to consider the pre-optimization of my own query before
> > launching it. As a functional language, I s
On 28 Nov 2013, at 11:22am, Tristan Van Berkom wrote:
> Yes, I definitely agree that on a conceptual level, I should not
> have to consider the pre-optimization of my own query before
> launching it. As a functional language, I should only have to
> describe the query and let SQLite do a better
On Thu, 2013-11-28 at 12:11 +0100, Clemens Ladisch wrote:
> Tristan Van Berkom wrote:
> > Are the JOIN statements equal to the logical AND statements,
>
> Yes.
>
Thank you.
> > for all practical purposes ?
>
> If you drop all those superfluous LEFT OUTER and IS NOT NULL parts,
> the database w
Tristan Van Berkom wrote:
> Are the JOIN statements equal to the logical AND statements,
Yes.
> for all practical purposes ?
If you drop all those superfluous LEFT OUTER and IS NOT NULL parts,
the database will be able to optimize the first query (the one
without subqueries) better.
Regards,
C
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 mat
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 sele
On Thu, 2013-11-28 at 00:20 -0500, Igor Tandetnik wrote:
> On 11/27/2013 11:52 PM, Tristan Van Berkom wrote:
> >
> > SELECT DISTINCT summary.uid, summary.vcard FROM 'folder_id' AS summary
> > LEFT OUTER JOIN 'folder_id_phone_list' AS phone_list
> >
On Thu, 2013-11-28 at 00:20 -0500, Igor Tandetnik wrote:
> On 11/27/2013 11:52 PM, Tristan Van Berkom wrote:
> >
> > SELECT DISTINCT summary.uid, summary.vcard FROM 'folder_id' AS summary
> > LEFT OUTER JOIN 'folder_id_phone_list' AS phone_list
> >
On 11/27/2013 11:52 PM, Tristan Van Berkom wrote:
SELECT DISTINCT summary.uid, summary.vcard FROM 'folder_id' AS summary
LEFT OUTER JOIN 'folder_id_phone_list' AS phone_list
ON phone_list.uid = summary.uid
LEFT OUTER JOIN 'folder_id_email_list
Hi,
I don't have many years experience with the SQL language
and I've cooked up some pretty complex stuff which will run
in production environments, I just want to confirm with you
that the assumptions I've made are true (I do have a lot of
unit tests which confirm that my code works as far as I
22 matches
Mail list logo
