Is flDeleted worth it?

Search feedlanddatabase for flDeleted and config.flCheckForDeleted, starting with the Atlantic river query and the feed-list code that shows five recent items. Compare the query behavior with and without the filter, then verify that the optional flag and deleted-item behavior in the feed list remain consistent with the issue's plan.

The item-level flDeleted boolean

There's an flDeleted column on the items table, that makes it possible to have a Delete command for feed items without data being deleted. This is something I design into products ever since I ran a BBS in the 80s and would get hacked by people logging in and deleting everything in the database. If I didn't actually delete anything that made restoring something I could do with one command.

But that was a long time ago, and there isn't a way in the UI to delete an item, so if we were to be hacked this way, they would have to break into SQL command mode, and then having an flDeleted boolean wouldn't help very much.

Why is this an issue?

It can make rivers a lot slower to build.

For example, here's the SQL code that builds a river for The Atlantic on feedland.org.

select * from items where flDeleted=false  and feedurl='https://www.theatlantic.com/feed/all/' order by pubDate desc limit 175;

It takes 5 seconds on feedland.org this morning.

If we remove the flDeleted=false check, it takes 0.6 seconds.

And I have the index that @scotthansonde recommended, indexing on flDeleted, pubDate and feedUrl.

Potential problem

I've looked elsewhere in feedlanddatabase, and there are other calls that use the flDeleted field, but the only one that actually is used in the UI is when you click the wedge next to a feed in the Feed List to reveal the five most recent items.

If in the future there's a UI for deleting an item, it may happen that you will see a deleted item in the 5-item list.

Plan

I'm going to remove the flDeleted check, with a big comment and a link to this issue in the comment.

Being cautious, I made it easy to turn this back on, with config.flCheckForDeleted which defaults false.

But I recommend when and if this feature needs to come back, we implement it by actually deleting the item in the database.