On 16.05.2018 00:12, Torsten Landschoff wrote:
> In case anybody would be so kind and take a look at this I included a
> small SQL Script to reproduce. Results here are:
>
> ----------
>> $ /opt/sqlite3/bin/sqlite3 -version
>> 3.23.2 2018-05-09 02:23:29
>> f139f6f07df094a0a62e0a55ae7e91dc68006d55c9db7b244a945cc8216f55ff
>> $ /opt/sqlite3/bin/sqlite3 < weird_plan.sql
Great, seems like the list strips attachments. Therefore I'll put the
code here - this is the content of weird_plan.sql:
-----
CREATE TABLE base (
id INTEGER NOT NULL PRIMARY KEY,
modification_time DATETIME NOT NULL
);
CREATE UNIQUE INDEX ix_base_mtime ON base (modification_time);
CREATE TABLE derived (id INTEGER NOT NULL PRIMARY KEY);
analyze sqlite_master;
INSERT INTO sqlite_stat1 VALUES('base','ix_base_mtime','5465697 1');
analyze sqlite_master;
select x'0a' || 'This results in a blazingly fast execution:' || x'0a';
explain SELECT base.id AS base_id, base.modification_time AS
base_modification_time
FROM base JOIN derived USING (id)
WHERE base.id < 5463052 order by derived.id desc
limit 100 offset 0;
select x'0a' || 'This takes seconds with real data:' || x'0a';
explain SELECT base.id AS base_id, base.modification_time AS
base_modification_time
FROM base JOIN derived USING (id)
WHERE base.id < 5463052 order by base.id desc
limit 100 offset 0;
-----
Greetings, Torsten
--
$---+----1----+----2----+----3----+----4----+----5----+----6----+
SCALE GmbH
Niederlassung Dresden
Torsten Landschoff
Pohlandstraße 19
01309 Dresden
Tel: +49-351-312002-10
Fax: +49-351-312002-29
SCALE GmbH
Registergericht und Sitz: Ingolstadt, HRB 6384
Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
