I have a table with 500,000+ records. The table has a date column, that I?m using to sort my queries (the columns has an index). Simple queries on the table work very well, using ORDER BY, LIMIT & OFFSET. I?m actually extracting ?pages? of rows that I?m displaying in a web page. Great!.
Now, instead of a simple select, I?d like to execute a recursive query using CTE, because the rows are organized in an hierarchy. And I?m only interested by a page, let's say the first n root records. Unfortunately, the doc says that the ?initial-select? in a recursive common table exception may not include ORDER BY, LIMIT or OFFSET. As a result SQLIte probably scans the whole table, which leads to very poor performance? With other databases (like PostgreSQL), I don?t have the problem because they accept ORDER BY, LIMIT and OFFSET on the initial-select, which limits the scan for the initial-select. What would be the proper solution with SQLite? One would involve a first query that selects the initial row ids, and then pass them as a condition to the initial-select in the recursive. But does anyone has a better proposal?
