Re: [sqlite] order of = in join

2014-02-01 Thread Klaas V
Anbrus wrote: "what's the easiest way to generate a set of fixed number of rows like VALUES does but in an order of your choice?" Order them before with the command 'sort' NAME sort - sort lines of text files SYNOPSIS sort [OPTION]... [FILE]... DESCRIPTION Write sorted

Re: [sqlite] order of = in join

2014-01-30 Thread Keith Medcalf
, 2014 09:25 >To: General Discussion of SQLite Database >Subject: [sqlite] order of = in join > >#1 - f.term = t.term >SELECT m.term AS term, m.definition AS definition >FROM >(SELECT t.term, e.definition >FROM Terms_content t >INNER JOIN Favorites f ON f.term = t.term

Re: [sqlite] order of = in join

2014-01-30 Thread Zsbán Ambrus
On 1/30/14, E. Timothy Uy wrote: > #1 - f.term = t.term > Query #1 takes 300 ms, and query #2 takes 30 ms. Can you show the schema for the tables? Is it possible that the two equals comparisons use different affinity or collation, which changes the semantics of your statement?

Re: [sqlite] order of = in join

2014-01-30 Thread Clemens Ladisch
E. Timothy Uy wrote: > Here the Terms_content table is big, maybe 300k rows, while the Favorites > table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 ms. Which SQLite version? What is the output of EXPLAIN QUERY PLAN for both queries? Regards, Clemens

Re: [sqlite] order of = in join

2014-01-30 Thread Simon Slavin
On 30 Jan 2014, at 4:24pm, E. Timothy Uy wrote: > Here the Terms_content table is big, maybe 300k rows, while the Favorites > table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 ms. Have you run ANALYZE on the database ? Simon.

[sqlite] order of = in join

2014-01-30 Thread E. Timothy Uy
#1 - f.term = t.term SELECT m.term AS term, m.definition AS definition FROM (SELECT t.term, e.definition FROM Terms_content t INNER JOIN Favorites f ON f.term = t.term LEFT JOIN TermEntries te ON te.termid = t.docid LEFT JOIN Entries e ON e.docid = te.entryid) AS m LEFT JOIN Favorites f ON f.term