SQLite Forum

```
SQLite version 3.36.0 2021-06-15 11:18:07
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table mqtt6
   ...> (
   ...>     ts integer not null,
   ...>     size_t integer not null
   ...> );
sqlite> create index mqtt6_ts_size_t on mqtt6 (ts, size_t);
sqlite> insert into mqtt6 select value, RandomV(1024) + 1 from generate_series where start == 1 and stop == 1000000;
sqlite> create trigger less_den_ten_mb_delete after insert on mqtt6 when (select sum(size_t) from mqtt6) > 1024000
   ...> begin
   ...>   delete from mqtt6
   ...>         where ts <= (
   ...>                        select ts
   ...>                          from (
   ...>                                select ts,
   ...>                                       sum(size_t) over (order by ts desc) as size
   ...>                                  from mqtt6
   ...>                               )
   ...>                         where size >= 1024000
   ...>                         limit 1
   ...>                     );
   ...> end;
sqlite> .timer on
sqlite> .eqp on
sqlite>
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 1000000  │ 512366354   │ 1       │ 1000000 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.147 user 0.140625 sys 0.000000
sqlite> insert into mqtt6 values (1000001, RandomV(1024)+1);
Run Time: real 0.504 user 0.500000 sys 0.015625
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023824     │ 997986  │ 1000001 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.004 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000002, RandomV(1024)+1);
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023316     │ 997987  │ 1000002 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000003, RandomV(1024)+1);
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2015     │ 1023479     │ 997989  │ 1000003 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000004, RandomV(1024)+1);
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023841     │ 997989  │ 1000004 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000005, RandomV(1024)+1);
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2017     │ 1023939     │ 997989  │ 1000005 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000006, RandomV(1024)+1);
Run Time: real 0.002 user 0.015625 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2017     │ 1023846     │ 997990  │ 1000006 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000007, RandomV(1024)+1);
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2017     │ 1023853     │ 997991  │ 1000007 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000008, RandomV(1024)+1);
Run Time: real 0.001 user 0.015625 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023964     │ 997993  │ 1000008 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000009, RandomV(1024)+1);
Run Time: real 0.002 user 0.015625 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023726     │ 997994  │ 1000009 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite>
```