On Wednesday, 10 April, 2019 08:12, Peng Yu <pengyu...@gmail.com> wrote:
>On 4/10/19, Keith Medcalf <kmedc...@dessus.com> wrote: >> The first column is of strings ... >> Do you mean a single string as in "KerfufledAllaHasbalah" >> Or a "bunch of strings with some implied delimiter" such as >> "Kerfufled/Alla/Hasballah" where "/" is the separator between >> strings? >> >> If the latter, the data needs to be normalized. >There is only one string in each row, not many strings separated by >some separator. A string can include white space characters. In other >words, a string can be a word or multiple words separated by white >spaces. >Would it be possible to create indexes at a subcolumn level? I think >if indexes can be created at the word level (or prefix of words, as I >frequently search for things like a noun and its plural form >together), then the search can be faster. There would be a number of ways that you can do this, including using the built-in FTS module. The "string" column is your free-form text. Be aware, however, that this will create and maintain a number of additional indexes on the data and will consume *more* disk space to hold the data and indexes, however search time will likely decrease from your current 23 seconds to about say 23 milliseconds for an appropriately phrased query yeilding similar results. (ie, the search will be 1000x faster but the space consumed may be only two or three times as much). You could also build your own "word" list by parsing the strings and linking them to the record to which they pertain, then use appropriately phrased queries to perform lightening fast searches. This is basically what FTS does, only you are doing it yourself. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
