While I take your point Ryan I think some of the examples are far removed from the case I highlighted. I mean if count(*) is the only query column and the RHS of the joins is ‘cross join t’ the optimisation would be simple. If the remaining joins end ‘cross join t2’ it could be optimised further and so on.
Is it worth sqlite checking for such specific cases? Very probably not. Like I said it doesn’t matter to me, I pointed it out just in case. ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of R Smith <ryansmit...@gmail.com> Sent: Thursday, March 15, 2018 11:25:18 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] count optimisation On 2018/03/15 12:20 PM, x wrote: > select count(*) from TblA cross join TblB > > is over 200 times slower than > > select (select count(*) from TblA) * (select count(*) from TblB); This is a human-level optimisation, it's not efficient for the database engine to do the optimisation. By human-level I mean it is in the same category as knowing that [((x + 1) / 50) * 100] - 2x] / 2 always evaluates to exactly 1 for all values of x. Replacing all that formula with *1* when compiling as an optimisation WILL WORK most definitely, but the number of such formulas we can come up with is infinite, there is no point having to ask the compiler to handle any one of those infinite variations as "special". Another silly example is a Query of the form: select American_President from [Any Table]; Which we can currently simply optimise with: select 'Donald Trump'; but you don't want the query engine to be doing that. Ever. To apply all of this to your specific case, how about if the query was in stead: select count(*), avg(col1) from TblA cross join TblB or indeed select count(*) from TblA, TblB CROSS JOIN TblC JOIN TblD ON 1=1 All these /can/ be optimised, but should be optimised by the programmer, not the Query engine. Cheers, Ryan _______________________________________________ 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
