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
