On 19 Aug 2010, at 10:39pm, Eric Smith wrote: > I want an index that only can be used to find rows with a particular > value or set of values.
Take a look at VIEWs: http://www.sqlite.org/lang_createview.html This is the SQL standard way to reduce your view of a table to just certain rows. If I understand your request, this feature should provide exactly what you want. Appropriate indexes will be used when consulting any VIEW you've defined. > Since SQLite doesn't support partial indices directly, I'm > thinking about making my own index as a separate table and > populating/depopulating it using triggers on the main table. I only > need it for fast lookups during deletion of the relevant rows, so I'll > hijack the app logic that wants to delete those rows and instead use > the secondary table to get the row ids, and delete those directly. > > Something like DELETE FROM records WHERE __recno IN (SELECT __recno > FROM idxTable), where __recno is the INTEGER PRIMARY KEY on records. I don't understand what you're looking up here. If you have some method of recognising which rows of a table should be deleted just use the appropriate DELETE FROM ... WHERE ... command. No need for any sub-SELECT clause. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
