SQLite search optimization across multiple tables with FTS5

I'm trying to optimize a search across multiple tables (specifically, two tables).

So far, I have been using the following query:

SELECT
  o.id, o.placedTime, cl.businessName, cl.companyName, cl.name, cl.businessNumber, o.note, cl.email
FROM orders o
LEFT JOIN clients cl ON o.clientId = cl.id
WHERE
  o.isRecurringOrder = 0 AND 
  (
    cl.businessName LIKE '%COOP%' OR 
    cl.companyName LIKE '%COOP%' OR 
    cl.name LIKE '%COOP%' OR 
    cl.businessNumber LIKE '%COOP%' OR
    o.note LIKE '%COOP%' OR
    cl.email LIKE '%COOP%'
  )
ORDER BY o.placedTime DESC

As the record count increases, the query is getting slower. The first thing I tried was adding indexes to the orders and clients tables.

When I ran the previous query with EXPLAIN QUERY PLAN, I got the following result:

If I’m not mistaken, the indexes are only being used for the orders table.

As a second solution, I explored an approach using a virtual FTS5 table. I implemented it for the clients table with the following command:

CREATE VIRTUAL TABLE clients_fts USING fts5(
    businessName, 
    companyName, 
    name, 
    businessNumber, 
    email, 
    content='clients', 
    content_rowid='id'
);

However, when I try to run the simplest query:

SELECT rowid, businessName, companyName, name 
FROM clients_fts 
WHERE clients_fts MATCH 'COOP';

I get no results.

I'm opend to any advice how to do it without the FTS5 table.

1. Regarding the issue with FTS5 returning no results - have you populated your FTS5 table with the data? (https://stackoverflow.com/a/69981377/11190073)

2. where xxx like '%yyy%' - no index and no embedded full-text search solutions of RDBMS(at least from my experience with MySQL and MariaDB fulltext solutions) yields better performance results, than search engines like Lucene/ElasticSearch/... Of course it hugely depends on data cardinality and your DB configuration - if your orders table is in hundreds/thousands/tensOfThousands, where o.note like '%COOP%' might perform just OK since your DB will probably cache all your data in memory, but if you're talking about hundredsOfThousands/millions and beyond, don't even consider where clause with like operand