results.txt

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

real	0m0,173s
user	0m0,158s
sys	0m0,012s

---------------------------------------------------------------
2 match:
QUERY PLAN
|--SCAN fts1 VIRTUAL TABLE INDEX 32:M6M5
|--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)

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

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);
-- aggregate table of searchable entities by fts
create table t1 (id integer primary key autoincrement, user_id integer, ic1 integer, ic2 integer, ic3 integer, ic4 integer, tc1 text, tc2 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 virtual table fts1 using fts5 (user_id unindexed, ic1, ic2, ic3 unindexed, ic4 unindexed, tc1, tc2, content='t1', 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)
        t1="bbb"
        t2="ccc"
        if ((j >= 1 && j <= 100)); then
            t1+=" aaa"
            t2+=" aaa"
        fi
        uid=$(( ( RANDOM % 10000 )  + 1 ))
        echo -en "${d}\n(${uid}, ${i}, ${i}, ${i}, ${i},'${t1}','${t2}')" >> /tmp/fts_values_buf
        ((++i))
    done
    V=$(cat /tmp/fts_values_buf)
    echo "insert into t1 (user_id, ic1, ic2, ic3, ic4, tc1, tc2) values ${V}; insert into fts1 (user_id, ic1, ic2, ic3, ic4, tc1, tc2) values ${V};" | sqlite3 a.db;
done;

fi

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