This is simplified from what I'm really doing and I'm having to type this from
memory, but I hope I've preserved enough that you can give the right answer.
I keep a log of changes made. It looks like this:
CREATE TABLE changes (
id INTEGER PRIMARY KEY,
dateTime TEXT,
theTable TEXT,
theRowID INTEGER,
theColumn TEXT,
theCommand TEXT)
CREATE INDEX changesTR ON changes (theTable,theRowID)
In 'theRowID' I have either a specific rowid, or '*' indicating that a change
effected more than one row. Rows in the 'changes' table are entered as changes
are made, in chronological order. No tricks.
I need to be able to search for /the most recent change/ which effected either
a specific row of a specific table. Which means I need the last record which
has either a specific number (123 for the sake of argument) or a '*' in.
1) My index does not specifically involve ordering the entries in 'id' order.
But entries in the table are always entered chronologically. Am I right that
SQLite implicitly adds the rowid into every index to keep entries unique ? In
other words, if I do
SELECT id FROM changes WHERE theTable='customers' AND theRowID=123 ORDER BY id
DESC LIMIT 1
will SQLite figure out that it can do this directly from the index ? Or should
I add the id column into the index ? If I added the id column in would it make
the index take up more space, or does SQLite know not to add the rowid if it
has already been specifically mentioned in the index ?
2) Can SQLite optimize searches across 'OR' ? In other words if I do
SELECT id FROM changes WHERE theTable='customers' AND (theRowID=123 OR
theRowID='*') ORDER BY id DESC LIMIT 1
will it use the index once for 123 and again for '*' ?
Thanks for any help or advice about the above, including people telling me
better and completely different ways to do it. None of this is fixed right now
and I can completely rewrite it if someone comes up with something better.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
