Generates rows with tokens 'bbb aaa' in 1/10 rows + int columns and explain queries with and w/o join.

results.txt

---------------------------------------------------------------
join t1:
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:M5
`--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)

real	0m0,178s
user	0m0,170s
sys	0m0,009s

---------------------------------------------------------------
w/o join:
QUERY PLAN
`--SCAN fts1 VIRTUAL TABLE INDEX 32:M5

real	0m0,207s
user	0m0,187s
sys	0m0,020s

sql_fts.sh

#!/bin/bash

# Run: ./sql_fts.sh 2>&1 | tee results.txt
# Run for rebuild db: rebuild=y ./sql_fts.sh 2>&1 | tee results.txt

if [[ "${rebuild}" != "" ]]; then

rm -f a.db

sqlite3 a.db <<EOF
create table t1 (id integer primary key autoincrement, ic1 integer, ic2 integer, ic3 integer, ic4 integer, tc text);
create index t1_ic1_ndx on t1 (ic1);
create index t1_ic3_ndx on t1 (ic3);
create virtual table fts1 using fts5 (ic1, ic2, ic3 unindexed, ic4 unindexed, tc, content='t1', content_rowid='id');
EOF

i=1
for k in {1..1000}; do
    echo "insert ${k} page"
    echo -n "" > /tmp/fts_values_buf
    for j in {1..1000}; do
        d=
        [ $j -gt 1 ] && d=", "
        # t=$(openssl rand -base64 20)
        t="bbb"
        if ((j >= 1 && j <= 100)); then
            t+=" aaa"
        fi
        echo -en "${d}\n(${i}, ${i}, ${i}, ${i}, '${t}')" >> /tmp/fts_values_buf
        ((++i))
    done
    V=$(cat /tmp/fts_values_buf)
    echo "insert into t1 (ic1, ic2, ic3, ic4, tc) values ${V}; insert into fts1 (ic1, ic2, ic3, ic4, tc) values ${V};" | sqlite3 a.db;
done;

fi

echo ""
echo "---------------------------------------------------------------"
echo "join t1:"
SQL="select * from fts1 join t1 on t1.id=fts1.rowid where fts1 match 'aaa' and t1.ic1 >= 999000 order by rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" > /dev/null

echo ""
echo "---------------------------------------------------------------"
echo "w/o join:"
SQL="select * from fts1 where fts1 match 'aaa' and ic1 >= 999000 order by rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" > /dev/null