Firstly, (a, idate) is unique. You have defined it as the PRIMARY KEY. Therefore your index `a_idate_idx` is redundant (even though you forgot to define it as unique, it is not required anyway, and defining it is just a waste of time and space, since it is already defined as a unique key to the table).
As I understand your query, you want the latest entry for each project "a" that has a row with b being like '%ioc-net%'.
There is nothing you can do about having to scan the entire table to find the list of projects since your like expression starts with a wildcard. Also, unless you have changed it, LIKE is case insensitive so no need to call the lower function on each row. You can probably take advantage of the SQLite3 min/max optimization to get rid of the second correlated subquery though and retrieve the results directly rather than having to traverse the entire table again -- I have no idea how much difference that will make.
Is the following any faster:
```
SELECT a, b, b, d, e, max(idate) AS idate
FROM t0 AS rec
WHERE a IN (SELECT DISTINCT a FROM t0 WHERE b LIKE '%ioc-net%')
GROUP BY a;
```