Hi,
I'm working on a profiler that uses SQLite to store and index the results.
Due to the nature of the task, the workload ends up splitting into
essentially three phases:
1. Bulk insertion of relatively large amounts of data (low hundreds of
megabytes to tens of gigabytes); a typical profile generates on the
order of 10MB of sampling data per second.
2. Indexing. The first phase normally runs without any indexes on the
sample tables, to maximize insertion throughput. In the second phase we
create the indexes we need to retrieve the samples efficiently. (We
found "batch-indexing" after the fact to generally lead to shorter
overall processing times.)
3. Browse the results. Once the samples are indexed, the user can look
around, analyze the results using queries, and so forth. This is
essentially read-only.
Phase 2 is crucial for quick retrieval, but at the same time it
essentially amounts to wait time for the user, so we'd like to get it
over with as quickly as possible. Now, looking at what happens in there,
I noticed that we often end up creating several indexes on the same
table back-to-back:
CREATE INDEX samples_by_time ON samples(time);
CREATE INDEX samples_by_name ON samples(name_id);
CREATE INDEX samples_by_type ON samples(type_id);
Each of these statements ends up scanning over the entire table once.
Since the tables in question are quite large, I would love to have some
way to create multiple indexes in one pass. A cursory examination of the
VDBE code produced for the CREATE INDEX statements indicates that
SQLite's backend should be capable of doing so (though I'm no expert),
but I don't see a way to express what I want in standard SQL.
So, is there any way to create multiple indexes for the same table in
one operation in SQLite? If not, how hard would it be to add?
Thanks,
-Fabian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
