On 10 Jun 2014, at 7:22pm, Fabian Giesen <fabi...@radgametools.com> wrote:
> 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.CREATE INDEX samples_by_time ON > samples(time); I presume you do your INSERTs inside a transaction. If not, you should. > CREATE INDEX samples_by_name 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. Not possible in current SQLite. No standard syntax for doing it in SQL. A quick google suggests that no other popular DBMS implements it. And ... I'm not sure it would be that much faster. The majority of the work it's doing is writing. Reading the existing table data is fast and easy, but writing new data and figuring out the pointers takes the majority of the time. Because of this I don't think it likely that this feature will be added to standard SQLite. Of course, if you really care about this you could add it yourself. To optimize this, when you create the table put the columns you're going to index near the beginning of the rows. SQLite stops reading the data of a table row once it has all the columns it needs. I don't know whether this will make a lot of difference but it won't do any harm. Also, store your time as a numeric value (Unix epoch or Julian day) rather than a string. Numbers are faster to handle than strings. INTEGERs are faster to handle than REALs. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
