On Wed, 2005-02-09 at 09:30 +1100, Chris Schirlinger wrote:
> > Doing a keyed search is no guarantee that you won't touch *every* single
> > page in the table, if the rows are inserted in random order. Try this:
> ...cut...
> > Assuming key is the key field you want, the records will be inserted into
> > wibble in key order. Selecting by key will then touch the least number of
> > pages, speeding up the select.
>
> Ahhh excellent idea, this seems similar to a clustered index, where
> the data is actually stored on disk in order. That is actually what
> we wanted, but SQLite didn't seem to support those sorts of index
>
Assuming you are not using an INTEGER PRIMARY KEY, you could
do something like this:
CREATE TABLE wibble2 AS
SELECT * FROM wibble ORDER BY <cluster-index>;
DROP TABLE wibble;
ALTER TABLE wibble2 RENAME TO wibble;
The above is perhaps a simplification, depending on your
schema. But you should get the idea. By default, SQLite
puts records into a table in the order in which they are
inserted. So if you insert them in cluster order, that's
the way they will appear in the table and you can take
advantage of locality of reference.
Of course, as you insert and delete items from the table,
it gradually becomes less and less clustered. But all
clustered tables work that way, do they not? So you
periodically rebuild the table to restore locality of
reference when things deteriorate too much.
Another trick you can pull is to create an index that
contains every column in the table with the cluster index
columns occuring first. That will double the size of your
database. But when SQLite can get all of the information it
needs out of the index it does not bother to consult the
table itself. So the index will always stay clustered.
--
D. Richard Hipp <[EMAIL PROTECTED]>
