the flow is :
1) prepare the statement "select feed_title, feed_url, feed_date from feeds
where feed_title like :searchTerm or feed_url like :searchTerm LIMIT :limit
2b) log (ex 2017-12-17 15:16:23 - execute)
3b) log (2017-12-17 15:17:20 - executed)
> pasting some logs, Im declaring again this happening only the first time,
> afterwards working without problem.
> 2017-12-17 15:16:23 - execute
> 2017-12-17 15:17:20 - executed
> 2017-12-19 14:53:35 - execute
> 2017-12-19 14:54:32 - executed
> 2017-12-19 20:14:23 - execute
> 2017-12-19 20:15:20 - executed
I’m missing something here. All these operations seem to take the same time … around a minute. Do you have figures for the different one(s) ?
The first time you run it, the table is read into memory. Subsequent searches don’t need disk access, so they're faster.
If you need to convince your user that all operations are fast, silently execute the SELECT command during startup so that the table is already in cache.
> 1) prepare the statement "select feed_title, feed_url, feed_date from feeds
> where feed_title like :searchTerm or feed_url like :searchTerm LIMIT :limit
> OFFSET :offset"
There is no way to optimize in SQL for WHERE clause with to LIKE clauses. The entire table must be read.
It may be that full text searching (SQLite FTS4 extension) would speed things up, but I don’t use it and have no experience. Perhaps someone who uses it would tell you.