I'm working on a pagination system where two tables need to be joined. At first my query looked like this:
SELECT table1.data1, table2.data2 FROM table1 JOIN table2 ON table1.rowid = table2.rowid WHERE table1.data1 = 10 ORDER BY table1.rowid DESC OFFSET 0 LIMIT 250 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 This returns exactly the same results, but the performance is much better because it only has to JOIN the rows that match the WHERE clause. Soon, the first problem appeared: in some cases the WHERE clause was on a column from table2, for example: WHERE table2.data2 = 10, for which the above query wouldn't work. I changed my code so that when it detects the WHERE is on table2, it uses this query: SELECT table1.data1, table2.data2 FROM ( SELECT table2.data2 WHERE table2.data2 = 10 ORDER BY table2.rowid DESC OFFSET 0 LIMIT 250 ) AS table2 JOIN table1 ON table2.rowid = table1.rowid Which is nothing more than the exact reverse of the previous query. When my code detects that WHERE needs BOTH table1 and table2, it uses the old (slow) 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. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
