results.txt

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

real	0m0,225s
user	0m0,212s
sys	0m0,012s

---------------------------------------------------------------
user join t1, fts1, t2, fts2:
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:M6
|--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH t2 USING INDEX t2_user_id_ndx (user_id=?)
`--SCAN fts2 VIRTUAL TABLE INDEX 0:=M6

real	0m0,218s
user	0m0,197s
sys	0m0,020s

---------------------------------------------------------------
user join t1, fts1, t2:
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:M6
|--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH t2 USING INDEX t2_user_id_ndx (user_id=?)

real	0m0,245s
user	0m0,217s
sys	0m0,028s

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

real	0m0,269s
user	0m0,257s
sys	0m0,012s

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 user (id integer primary key autoincrement, name string);
create table t1 (id integer primary key autoincrement, user_id integer, ic1 integer, ic2 integer, ic3 integer, ic4 integer, tc text);
create table t2 (id integer primary key autoincrement, user_id integer, ic1 integer, ic2 integer, ic3 integer, ic4 integer, tc text);
create index t1_user_id_ndx on t1 (user_id);
create index t1_ic1_ndx on t1 (ic1);
create index t1_ic3_ndx on t1 (ic3);
create index t2_user_id_ndx on t2 (user_id);
create index t2_ic1_ndx on t2 (ic1);
create index t2_ic3_ndx on t2 (ic3);
create virtual table fts1 using fts5 (user_id unindexed, ic1, ic2, ic3 unindexed, ic4 unindexed, tc, content='t1', content_rowid='id');
create virtual table fts2 using fts5 (user_id unindexed, ic1, ic2, ic3 unindexed, ic4 unindexed, tc, content='t2', content_rowid='id');
EOF

    echo "generate user table"
    for i in {1..10000}; do
        if ((i % 100 == 0)); then echo "insert ${i} records"; fi
        sqlite3 a.db "insert into user(id, name) values (${i}, 'user_${i}');"
    done

    echo "generate t1, t2, fts1, fts2 tables"
    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
            uid=$(((RANDOM % 10000) + 1))
            echo -en "${d}\n(${uid}, ${i}, ${i}, ${i}, ${i}, '${t}')" >>/tmp/fts_values_buf
            ((++i))
        done
        V=$(cat /tmp/fts_values_buf)
        echo "insert into t1 (user_id, ic1, ic2, ic3, ic4, tc) values ${V}; insert into fts1 (user_id, ic1, ic2, ic3, ic4, tc) values ${V};" | sqlite3 a.db
        echo "insert into t2 (user_id, ic1, ic2, ic3, ic4, tc) values ${V}; insert into fts2 (user_id, ic1, ic2, ic3, ic4, tc) values ${V};" | sqlite3 a.db
    done

fi

echo ""
echo "---------------------------------------------------------------"
echo "user join t1, fts1:"
jt1="join t1 on t1.user_id=u.id join fts1 on fts1.rowid=t1.id"
# jt2="join t2 on t2.user_id=u.id join fts2 on fts2.rowid=t2.id"
SQL="select u.* from user as u ${jt1} where fts1 match 'aaa' and t1.ic1 >= 999000 and t1.ic3 >= 999900 order by rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" >/dev/null

echo ""
echo "---------------------------------------------------------------"
echo "user join t1, fts1, t2, fts2:"
jt1="join t1 on t1.user_id=u.id join fts1 on fts1.rowid=t1.id"
jt2="join t2 on t2.user_id=u.id join fts2 on fts2.rowid=t2.id"
SQL="select u.* from user as u ${jt1} ${jt2} where fts1 match 'aaa' and t1.ic1 >= 999000 AND fts2 match 'aaa' and t2.ic3 >= 999900 order by fts1.rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" >/dev/null

echo ""
echo "---------------------------------------------------------------"
echo "user join t1, fts1, t2:"
jt1="join t1 on t1.user_id=u.id join fts1 on fts1.rowid=t1.id"
jt2="join t2 on t2.user_id=u.id"
SQL="select u.* from user as u ${jt1} ${jt2} where fts1 match 'aaa' and t1.ic1 >= 999000 AND t2.ic3 >= 999900 order by fts1.rank limit 10;"
sqlite3 a.db "EXPLAIN QUERY PLAN ${SQL}"
time sqlite3 a.db "${SQL}" >/dev/null

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