3 ms·

i don't follow why you need an extra table for each index. why can't you just define an index on the existing columns in the original table? also, why do you

i don't follow why you need an extra table for each index. why can't you just define an index on the existing columns in the original table? also, why do you manage the use of indices yourself? you say that you scan the largest index first. but if you just use sql indices then sql should do whatever is smartest automatically. perhaps what i am missing is how you are storing the schemaless data? does that somehow drive the above? but even if that drives the need for two tables (one 'schemaless' in some way, and one for indices) you can get by with only a single extra table for indices (i think) - just add columns using "alter table". and that will allow the sql engine to select indices for you.

StavrosK 15y ago
Well, the main table has a uuid and a blob that is a pickled dictionary. Your point about the extra table is correct, we can have one table with all the fields to be indexed in it and then just have SQLite create indexes on the groups we want. I'll have to think a bit about that implementation, but it's most likely that that's the design that will win in the end, unless there's a complication that means it can't be done that way (however, I don't see a problem with it now).
jsherer 15y ago
The problem here is specifically with how SQLite does not implement ALTER TABLE fully. From the documentation: "SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table." http://www.sqlite.org/lang_altertable.html http://www.sqlite.org/lang_altertable.html
- StavrosK 15y ago
  Ah, too bad, I was thinking how this could be done with one table. It sounds like it would be too much of a hassle, as it would keep growing. I don't think it's too much of a problem to have different tables, though, most people would have single indexes, I guess.

StavrosK 15y ago

Well, the main table has a uuid and a blob that is a pickled dictionary. Your point about the extra table is correct, we can have one table with all the fields to be indexed in it and then just have SQLite create indexes on the groups we want. I'll have to think a bit about that implementation, but it's most likely that that's the design that will win in the end, unless there's a complication that means it can't be done that way (however, I don't see a problem with it now).

jsherer 15y ago

The problem here is specifically with how SQLite does not implement ALTER TABLE fully. From the documentation: "SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table." http://www.sqlite.org/lang_altertable.html http://www.sqlite.org/lang_altertable.html

StavrosK 15y ago
Ah, too bad, I was thinking how this could be done with one table. It sounds like it would be too much of a hassle, as it would keep growing. I don't think it's too much of a problem to have different tables, though, most people would have single indexes, I guess.