Re: [sqlite] Fast JOIN
2011/10/19 Fabian > > > Maybe there is just no easy way to do what I want, I will take a good night > sleep about it ;) > > I think the correct query would be: SELECT table1.data1, table2.data2 FROM ( SELECT table1.rowid, table1.data1 WHERE table1.data1 = 10 ) AS table1 JOIN table2 ON table1.rowid = table2.rowid ORDER BY table2.data2 DESC OFFSET 0 LIMIT 250 Which should make sure that only the rows matching WHERE are going to be sorted (instead of all rows). Since I moved the TEXT columns out of my main table to a FTS table, I have all these complex and inefficient joins, because all data is divided between two tables with identical rowids. I hope some day FTS supports INTEGER columns, so I can merge the two back together, and get rid of the joins :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fast JOIN
you will need an inner select that selects everything and orders that, then an outside select that pages from the inner select. Woody Wizard, at large "I'm in shape, round is a shape!" --- On Tue, 10/18/11, Fabian wrote: From: Fabian Subject: Re: [sqlite] Fast JOIN To: "General Discussion of SQLite Database" Date: Tuesday, October 18, 2011, 10:17 PM > > > Assuming that is a typo, does the following not do what you want? > > SELECT table1.data1, table2.data2 > FROM > ( > SELECT table1.rowid, table1.data1 > WHERE table1.data1 = 10 > ORDER BY table1.rowid DESC > OFFSET 0 LIMIT 250 > ) AS table1 > JOIN table2 > ON table1.rowid = table2.rowid > ORDER BY table2.rowid; > > No, because it only sorts the current page. When the user scrolls down using the scrollbar, and the second page is fetched (OFFSET 250 LIMIT 250), it does not match up with the sorting of the previous page. Suppose it was sorted by a TEXT column of table2, it would look like this: Page 1: aaa abb ddd Page 2: bba cca dda Maybe there is just no easy way to do what I want, I will take a good night sleep about it ;) ___ 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] Fast JOIN
> > > Assuming that is a typo, does the following not do what you want? > > SELECT table1.data1, table2.data2 > FROM > ( > SELECT table1.rowid, table1.data1 > WHERE table1.data1 = 10 > ORDER BY table1.rowid DESC > OFFSET 0 LIMIT 250 > ) AS table1 > JOIN table2 > ON table1.rowid = table2.rowid > ORDER BY table2.rowid; > > No, because it only sorts the current page. When the user scrolls down using the scrollbar, and the second page is fetched (OFFSET 250 LIMIT 250), it does not match up with the sorting of the previous page. Suppose it was sorted by a TEXT column of table2, it would look like this: Page 1: aaa abb ddd Page 2: bba cca dda Maybe there is just no easy way to do what I want, I will take a good night sleep about it ;) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fast JOIN
On 18 October 2011 22:09, Fabian wrote: > I'm working on a pagination system where two tables need to be joined. . . . > > I got very good advice on this mailing-list, to change the query into this: > > SELECT table1.data1, table2.data2 > FROM > ( > > SELECT table1.data1 > WHERE table1.data1 = 10 > ORDER BY table1.rowid DESC > OFFSET 0 LIMIT 250 > > ) AS table1 > JOIN table2 > ON table1.rowid = table2.rowid . . . > JOIN. So far, so good. > > Then the second problem appeared: the user is free to sort on any column of > the output. So you can have the situation where the WHERE is on table1 but > ORDER BY is on table 2. At first I wanted to solve this by using the old > (slow) JOIN for cases like this. But it turned out to be unworkable: if the > query only returns 3 rows, it was still spending precious seconds sorting > all the other rows in the table. > > Is there any solution for this? All things I tried completely break the > pagination system, because it fully depends on having ORDER and OFFSET in > the same sub-query. Your queries above produce syntax errors, as there is no rowid from the inner subquery on which to perform the join. Assuming that is a typo, does the following not do what you want? SELECT table1.data1, table2.data2 FROM ( SELECT table1.rowid, table1.data1 WHERE table1.data1 = 10 ORDER BY table1.rowid DESC OFFSET 0 LIMIT 250 ) AS table1 JOIN table2 ON table1.rowid = table2.rowid ORDER BY table2.rowid; Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fast JOIN
On Oct 18, 2011, at 11:09 PM, Fabian wrote: > Is there any solution for this? Perhaps this is not a technical issue, but rather a design one, as you seem to be, hmmm, tilting at windmills. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
