[chaley]

It is indeed almost certainly the (lack of) indices.

Here is the query plan after upgrade from V3.3.6 to V3.4.2.

EXPLAIN QUERY PLAN FOR getAllBooks  SELECT id FROM  Categories , Books , cat_books_link WHERE cat_group =? AND cat_name =? AND  cbl_category = cat_id AND id = cbl_book ORDER BY author_sort ASC,title_sort ASC,title_sort ASC  ;
QUERY PLAN getAllBooks: 0:0:0:SCAN TABLE Categories (~10000 rows)
QUERY PLAN getAllBooks: 0:1:2:SEARCH TABLE cat_books_link USING AUTOMATIC COVERING INDEX (cbl_category=?) (~7 rows)
QUERY PLAN getAllBooks: 0:2:1:SEARCH TABLE Books USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
QUERY PLAN getAllBooks: 0:0:0:USE TEMP B-TREE FOR ORDER BY

Note the first QUERY PLAN line. It is using a scan.

Here is the query plan for V3.4.2 after recreating the indices

EXPLAIN QUERY PLAN FOR getAllBooks  SELECT id FROM  Categories , Books , cat_books_link WHERE cat_group =? AND cat_name =? AND  cbl_category = cat_id AND id = cbl_book ORDER BY author_sort ASC,title_sort ASC,title_sort ASC  ;
QUERY PLAN getAllBooks: 0:0:0:SEARCH TABLE Categories USING INDEX cat_group_fl (cat_group=?) (~2 rows)
QUERY PLAN getAllBooks: 0:1:2:SEARCH TABLE cat_books_link USING COVERING INDEX cbl_lines (cbl_category=?) (~10 rows)
QUERY PLAN getAllBooks: 0:2:1:SEARCH TABLE Books USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
QUERY PLAN getAllBooks: 0:0:0:USE TEMP B-TREE FOR ORDER BY

Now the first query in the plan uses an index. In addition, the second part of the query now uses a permanent index instead of an automatic one that will be dropped from time to time.

There are similar examples with other queries.

The categories table is large, one line per unique item (author, publisher, tag, etc). Scanning that table on a large database is certainly suboptimal.

I will add a rebuild of the indices to the database vacuum function. I will also be sure to rebuild the indices on the next CC release.