results.txt

---------------------------------------------------------------
user join 1 entity:
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,248s
user	0m0,239s
sys	0m0,009s

---------------------------------------------------------------
user join 2 entities:
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,198s
user	0m0,190s
sys	0m0,008s

---------------------------------------------------------------
user join 5 entities:
QUERY PLAN
|--SCAN fts2 VIRTUAL TABLE INDEX 0:M6
|--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH t4 USING INDEX t4_user_id_ndx (user_id=?)
|--SEARCH t5 USING INDEX t5_user_id_ndx (user_id=?)
|--SEARCH t1 USING INDEX t1_user_id_ndx (user_id=?)
|--SEARCH t3 USING INDEX t3_user_id_ndx (user_id=?)
|--SCAN fts1 VIRTUAL TABLE INDEX 32:=M6
|--SCAN fts3 VIRTUAL TABLE INDEX 0:=M6
|--SCAN fts4 VIRTUAL TABLE INDEX 0:=M6
|--SCAN fts5 VIRTUAL TABLE INDEX 0:=M6
`--USE TEMP B-TREE FOR ORDER BY

real	0m0,045s
user	0m0,037s
sys	0m0,008s

---------------------------------------------------------------
user join 10 entities:
QUERY PLAN
|--SCAN fts9 VIRTUAL TABLE INDEX 0:M6
|--SEARCH t9 USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH t6 USING INDEX t6_user_id_ndx (user_id=?)
|--SEARCH t3 USING INDEX t3_user_id_ndx (user_id=?)
|--SEARCH t7 USING INDEX t7_user_id_ndx (user_id=?)
|--SEARCH t8 USING INDEX t8_user_id_ndx (user_id=?)
|--SEARCH t2 USING INDEX t2_user_id_ndx (user_id=?)
|--SEARCH t5 USING INDEX t5_user_id_ndx (user_id=?)
|--SEARCH t10 USING INDEX t10_user_id_ndx (user_id=?)
|--SEARCH t4 USING INDEX t4_user_id_ndx (user_id=?)
|--SEARCH t1 USING INDEX t1_user_id_ndx (user_id=?)
|--SCAN fts8 VIRTUAL TABLE INDEX 0:=M6
|--SCAN fts1 VIRTUAL TABLE INDEX 32:=M6
|--SCAN fts2 VIRTUAL TABLE INDEX 0:=M6
|--SCAN fts3 VIRTUAL TABLE INDEX 0:=M6
|--SCAN fts4 VIRTUAL TABLE INDEX 0:=M6
|--SCAN fts5 VIRTUAL TABLE INDEX 0:=M6
|--SCAN fts6 VIRTUAL TABLE INDEX 0:=M6
|--SCAN fts7 VIRTUAL TABLE INDEX 0:=M6
|--SCAN fts10 VIRTUAL TABLE INDEX 0:=M6
`--USE TEMP B-TREE FOR ORDER BY

real	0m0,025s
user	0m0,020s
sys	0m0,004s

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

    tables=""
    for i in {1..10}; do
        tables+="
create table t${i} (id integer primary key autoincrement, user_id integer, ic1 integer, ic2 integer, ic3 integer, ic4 integer, tc text);
create index t${i}_user_id_ndx on t${i} (user_id);
create index t${i}_ic1_ndx on t${i} (ic1);
create index t${i}_ic3_ndx on t${i} (ic3);
create virtual table fts${i} using fts5 (user_id unindexed, ic1, ic2, ic3 unindexed, ic4 unindexed, tc, content='t${i}', content_rowid='id');
"
    done

    sqlite3 a.db <<EOF
create table user (id integer primary key autoincrement, name string);
EOF
    sqlite3 a.db "${tables}"

    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, .., t10, fts1, fts2, .., fts10 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)
        for i in {1..10}; do
            echo "insert into t${i} (user_id, ic1, ic2, ic3, ic4, tc) values ${V}; insert into fts${i} (user_id, ic1, ic2, ic3, ic4, tc) values ${V};" | sqlite3 a.db
        done
    done

fi

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"
jt3="join t3 on t3.user_id=u.id join fts3 on fts3.rowid=t3.id"
jt4="join t4 on t4.user_id=u.id join fts4 on fts4.rowid=t4.id"
jt5="join t5 on t5.user_id=u.id join fts5 on fts5.rowid=t5.id"
jt6="join t6 on t6.user_id=u.id join fts6 on fts6.rowid=t6.id"
jt7="join t7 on t7.user_id=u.id join fts7 on fts7.rowid=t7.id"
jt8="join t8 on t8.user_id=u.id join fts8 on fts8.rowid=t8.id"
jt9="join t9 on t9.user_id=u.id join fts9 on fts9.rowid=t9.id"
jt10="join t10 on t10.user_id=u.id join fts10 on fts10.rowid=t10.id"

echo ""
echo "---------------------------------------------------------------"
echo "user join 1 entity:"
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 2 entities:"
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 5 entities:"
sql="select u.* from user as u ${jt1} ${jt2} ${jt3} ${jt4} ${jt5}
where fts1 match 'aaa' and t1.ic1 >= 999000
  and fts2 match 'aaa' and t2.ic3 >= 999900
  and fts3 match 'aaa' and t3.ic3 >= 999900
  and fts4 match 'aaa' and t4.ic3 >= 999900
  and fts5 match 'aaa' and t5.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 10 entities:"
sql="select u.* from user as u ${jt1} ${jt2} ${jt3} ${jt4} ${jt5} ${jt6} ${jt7} ${jt8} ${jt9} ${jt10}
where fts1 match 'aaa' and t1.ic1 >= 999000
  and fts2 match 'aaa' and t2.ic3 >= 999900
  and fts3 match 'aaa' and t3.ic3 >= 999900
  and fts4 match 'aaa' and t4.ic3 >= 999900
  and fts5 match 'aaa' and t5.ic3 >= 999900
  and fts6 match 'aaa' and t6.ic3 >= 999900
  and fts7 match 'aaa' and t7.ic3 >= 999900
  and fts8 match 'aaa' and t8.ic3 >= 999900
  and fts9 match 'aaa' and t9.ic3 >= 999900
  and fts10 match 'aaa' and t10.ic3 >= 999900
order by fts1.rank limit 10;"
sqlite3 a.db "explain query plan ${sql}"
time sqlite3 a.db "${sql}" >/dev/null