Hi, I'm working on using sqlite for Firefox's bookmarks and history system. We need to expire history older than N days when the browser shuts down. It's currently slower than I would like and I'd appreciate any tricks you might be able to suggest.
Database layout: We have a table of URLs and titles called moz_history. Then there is a separate table called moz_historyvisit that associates a date and some other stats with the ID of the correct entry in moz_history. There may be many visits for each URL (each time you visit the page). We have a table of bookmarks that references the history table as well. Deleting the visits is easy, we have an index over the dates so we just say "DELETE FROM moz_historyvisit WHERE visit_date < ?1" This seems fast enough. Now we need to expire history entries that have no visits, are not bookmarked, and do not begin with "place:" (which are used for internal stuff). The simple command is DELETE from moz_history WHERE id NOT IN (SELECT page_id FROM moz_historyvisit) AND id NOT IN (SELECT page_id FROM moz_bookmarks) AND SUBSTR(url,0,6) <> 'place:'; This is pretty slow. We have indices over page_ids in both bookmarks and visits. The string compare seems like it might be slow but it doesn't seem to matter much whether we keep it in or not (99% of the URLs will fail after the first character compare because they begin with 'http:'). I tried some other things like creating a trigger that sets a flag in moz_history when a visit has been deleted so we only have to check those items, but it still seems slow. Can anybody offer any insight on this? I'm not really a SQL expert. Thanks in advance, Brett
