Slow display of the list of items with 800+ entries

[pmartin]

I have more than 800 entries in the "unread" list on my device (I've been using the app without purging the local storage for a while now -- and each time I sync I fetch more new unread entries than I read... so the number of unread entries on my device is bound to grow all the time.

But now, displaying the list of entries takes a bit of time ; not sure how to time it exactly, but I would say between 500 ms and 1 second, which is definitely enough to be noticeable :-(
This slowness happens everytime the list of entries is to be displayed / updated:

• When I start the app
• When I use the physical buttons to switch between pages in the list
• When the list is re-displayed after reading an entry

Note I also have some "archived" entries (a few dozens?) and a few "starred" entries, if that matters.

[pmartin]

A few ideas:

• Maybe a missing index on the SQLite DB? Like an index that would help with filtering/sorting of the list? Judging from the migrations there only is an index on the local_updated_at field, but it seems the list filters by local_is_archived = 0 and then orders by remote_created_at desc, which would indicate there is no index used there ;-(. Warning: maybe another index would help with archived/starred lists?
• Even if this is not a perfect solution, I would be OK with automatically removing old entries (even unread one) from the device when running a sync -- maybe keep no more than 500 unread entries? Ensuring the number of entries on the devices doesn't grow forever would actually be a good idea ^^

[pmartin]

Quick recap of the SQL queries done to display the lists of items :

For unread entries:

...
where 
    local_is_archived = 0 
    and (local_is_starred = 0 or local_is_starred = 1)
order by remote_created_at desc

For archived entries:

...
where 
    local_is_archived = 1 
    and (local_is_starred = 0 or local_is_starred = 1)
order by remote_created_at desc

And for starred entries:

...
where 
    (local_is_archived = 0 or local_is_archived = 1)
    and local_is_starred = 1
order by remote_created_at desc

An index on remote_created_at desc would be useful for the order by clause.
Indexes on local_is_archived and local_is_starred might help a bit, but their cardinality will be awful, so not too sure...

[pmartin]

In the documentation I see the following info, which might indicate SQLite only uses one index per table?

First, in the from section:

Each table in the FROM clause of a query can use at most one index [...] and SQLite strives to use at least one index on each table.

And then in the order by section:

SQLite attempts to use an index to satisfy the ORDER BY clause of a query when possible. When faced with the choice of using an index to satisfy WHERE clause constraints or satisfying an ORDER BY clause, SQLite does the same cost analysis described above and chooses the index that it believes will result in the fastest answer

Judging from this, I would either:

• Set an index only on remote_created_at, to help with the order by
• Or set an index on local_is_archived + remote_created_at, which could help for the "unread" and "archived" list, but might not help for the "starred" list -- the later one not being used that often I guess.

The second option is actually what is described in Searching And Sorting With A Multi-Column Index, I think, and might be a good idea.