Out of interest, is all the data in the artnr field numeric? If so, why are you storing it as text and not an integer? Integer searching is much, much faster.
Nick. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Thomas Lenders Sent: 26 November 2009 14:50 To: General Discussion of SQLite Database Subject: Re: [sqlite] Huge Table with only one field -- efficient way to create index ? Simon Slavin schrieb: > On 26 Nov 2009, at 2:04pm, Thomas Lenders wrote: > > >> I am using SQLite on a mobile device. >> >> I have this one table which has only one field, but I need to search in >> the table very quickly. >> When creating an index on this field the size of the database is doubled >> - which makes sense. >> >> However, is there some way to implement this scenario more efficiently >> using SQLite ? >> > > When you talk about searching, are you talking about searching in an ordered way (e.g. all the records in alphabetical order) or are you matching on content using something like LIKE '%fred%' ? For the LIKE matching, no INDEX is used. > > If you have just one column in the field, and don't need it indexed, do you really need SQL at all ? Could you not just store the data as a text file ? > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Thanks for your reply. I am looking for an exact match as in I need to know if a number exists in the table or not. Something like "select count(*) from ART where artnr='0123456789'". I could store the data in a text file instead but then I would have to search in the textfile on "disk", eg. a binary search algorithm or something. I cannot just load the textfile into memory because in this case, the storage space on "disk" and the available memory actually come from the same pool I would still have to store the data twice. Plus, there are other tables I use as well so I will use SQLite anyway. Would be rather nice to be able to use it for this special "table" as well. The catch is, if I search without having an index it will take 5+ seconds to find the record which sadly isnt fast enough. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
