Simon, I’ve no users. I’ve been teaching myself c++ (and SQLite) during a lengthy illness so this is just experimentation for me.
This subject touches on a previous question of mine you were involved in regarding redundant tables. The following may jog your memory create table TblA(A integer primary key, B int, C int); create table TblB(B integer primary key, BX int); create table TblC(C integer primary key, CX int); explain query plan select A from TblA left join TblB using (B) left join TblC using (C) where BX=?; 0|0|0|SCAN TABLE TblA 0|1|1|SEARCH TABLE TblB USING INTEGER PRIMARY KEY (rowid=?) explain query plan select A from TblA left join TblB using (B) left join TblC using (C) where CX=?; 0|0|0|SCAN TABLE TblA 0|1|1|SEARCH TABLE TblB USING INTEGER PRIMARY KEY (rowid=?) 0|2|2|SEARCH TABLE TblC USING INTEGER PRIMARY KEY (rowid=?) In the first explain SQLite drops the trailing redundant table but in the second explain it doesn’t drop the middle redundant table. As TblB is included in the second query it must surely run slower than if it were omitted. I’m not complaining about the SQLite optimiser failing to spot the redundancy as it’s got to deal with a variety of queries far removed from my narrow experiment. Checking for such redundancies would likely slow down prepares and, when it comes down to it, anyone including TblB in the second query is only getting what they asked for. I do think though that it’s possible to write code to remove these redundancies so as to get the vector of RowIDs as fast as possible. So far I’ve been splitting SQL into ‘RowSQL’ (returns RowIDs involved in correct order) and ‘ColSQL’ (returns columns requested in original SQL for the requested range as shown in my second post) but I’ve only been doing it visually via knowledge of the tables. What I’m trying to do is write a function to automatically ‘split’ the sql into RowSQL and ColSQL. I’ll make another post later showing where I’m at with that. ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Simon Slavin <slav...@bigfraud.org> Sent: Saturday, November 25, 2017 1:26:00 PM To: SQLite mailing list Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set On 25 Nov 2017, at 1:15pm, curmudgeon <tam118...@hotmail.com> wrote: > Given a select where a 'base table' is attached to lookup tables > how can I determine which of the lookup tables can be removed from the table > such that > > select BaseTbl.RowID from ... where ... order by ... > > will find the set of records that represents the original query. That optimization could be done at the level of the SQL engine. You wouldn’t want to do it inside your own code since that would make your code extremely complicated. So just execute the query without trying to optimize it and see what happens. Does it run fast enough for your users ? Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
