> How does the performance compare with this:
>
> CREATE TABLE t1 (a TEXT, b TEXT);
> CREATE TABLE t2 (a TEXT, b TEXT);
> CREATE INDEX t1_a ON t1 (a,b);
> CREATE INDEX t2_a ON t2 (a,b);
>
> SELECT *
> FROM t1 LEFT JOIN t2 ON (t1.b=t2.b AND t2.a='123')
> WHERE t1.a='123';
>
Here are best-of-three averages for my version and your version for both
the good and bad commits:
good / mine :: <0.1s
bad / mine :: 12.5s
good / yours :: 0.2s
bad / yours :: 0.2s
In other words, there is no regression for your version. But it does not
return as quickly as my version does before the regression.
> Run "ANALYZE;" on a database that contains actual data, then send us
> the output of ".fullschema"
>
Here you go:
sqlite> analyze;
sqlite> .fullschema
CREATE TABLE t1 (a TEXT, b TEXT);
CREATE TABLE t2 (a TEXT, b TEXT);
CREATE INDEX t1_a ON t1 (a);
CREATE INDEX t1_b ON t1 (b);
CREATE INDEX t2_a ON t2 (a);
CREATE INDEX t2_b ON t2 (b);
ANALYZE sqlite_master;
ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('t2','t2_b','1000000 1000');
INSERT INTO sqlite_stat1 VALUES('t2','t2_a','1000000 1000');
INSERT INTO sqlite_stat1 VALUES('t1','t1_b','1000000 1000');
INSERT INTO sqlite_stat1 VALUES('t1','t1_a','1000000 1000');
ANALYZE sqlite_master;
The sqlite_stat1 values make sense, because there are 1,000,000 rows in
each of t1 and t2, with 1,000 values for each of a and b.
After running ANALYZE, the origin query returns in 2.7s, but the query plan
is unchanged.
On Mon, 6 Jan 2020 at 01:53, Richard Hipp <d...@sqlite.org> wrote:
> How does the performance compare with this:
>
> CREATE TABLE t1 (a TEXT, b TEXT);
> CREATE TABLE t2 (a TEXT, b TEXT);
> CREATE INDEX t1_a ON t1 (a,b);
> CREATE INDEX t2_a ON t2 (a,b);
>
> SELECT *
> FROM t1 LEFT JOIN t2 ON (t1.b=t2.b AND t2.a='123')
> WHERE t1.a='123';
>
>
> On 1/5/20, Peter Inglesby <peter.ingle...@gmail.com> wrote:
> > Is there any more information I could provide?
>
> Run "ANALYZE;" on a database that contains actual data, then send us
> the output of ".fullschema"
>
> --
> D. Richard Hipp
> d...@sqlite.org
> _______________________________________________
> 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
