Re: [sqlite] order of = in join
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 concatenation of all FILE(s) to standard output. Mandatory arguments to long options are mandatory for short options too. Ordering options: -b, --ignore-leading-blanks ignore leading blanks -d, --dictionary-order consider only blanks and alphanumeric characters -f, --ignore-case fold lower case to upper case characters HTH, Klaas `Z4us` V ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of = in join
What version of SQLite and what indexes have you created? (and, if you have relevant indexes, have you run analyze?) >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of E. Timothy Uy >Sent: Thursday, 30 January, 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 >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 = m.term" > >#2 - t.term = f.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 t.term = f.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 = m.term > > >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. >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of = in join
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? Ambrus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of = in join
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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of = in join
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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 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 = m.term" #2 - t.term = f.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 t.term = f.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 = m.term 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
