On 24 Nov 2010, at 2:18pm, Paul Sanderson wrote: > Unfortunately all of the columns are represented in a grid that users > can choose to sort on any column, although they are less likely to > sort on certain columns, if they do chose to do so then an excessive > delay is not really acceptable.
Okay, that explains that. > Currently I create ascending and descending indexes for each column > individually i.e. > > create index if not exists name_a on table(name asc) > create index if not exists name_d on table(name desc) > etc. Well at least I can save you /some/ time. You can do just the ascending ones. Searches which require descending order should automatically use the ascending index if it will be of any use to them. Descending indexing are usually needed only for compound indexes like create index if not exists name_birthday on table(surname asc, birthdate desc) where one key needs to be searched ascending and the other descending. Obviously, try it first. Create just the ascending indexes and see whether descending sorts take longer than ascending ones. > The data is entered in order for the primary index > > File size varies from case to case but typically prior to indexing it > is about 300MB and with indexes 600MB. Mmm. I think that apart from the above you're doing everything right, and that without extreme and complicated measure you could improve your time only by some very small percentage. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
