passiomatic / coldsweat

Web RSS aggregator and reader compatible with the Fever API
MIT License
146 stars 21 forks source link

Optimize database queries #15

Open passiomatic opened 11 years ago

passiomatic commented 11 years ago

Several queries spawn quite a lot of subqueries due to the way they are constructed. The obvious ones in fever.py are:

The underlying problem is that xxx.icon.id and xxx.feed.id fields are not formally retrieved by Peewee, even if they are there in the record, so an extra query per row is performed.

The Peewee .switch(model) method could do the trick: http://peewee.readthedocs.org/en/latest/peewee/api.html#Query.switch

passiomatic commented 10 years ago

One more optimization could be this: every time an entry listing is displayed the system requires the list of saved ids, read ids and the unread|saved|all entry listing.

A more optimized query could retrieve the unread entries and also perform a left outer join on Saved model to figure out which entry are saved or not.

In the same vein another query could retrieve the saved entries and also perform a left outer join on Read model to figure out which entry have been read or not.

Finally the query to retrieve all entries could both left outer join Saved and Read models.