Hey guy, you are doing wrong, you don't need the booth indexes in same field;
DROP TABLE tb; CREATE TEMP TABLE tb ( a INTEGER, b TEXT, CONSTRAINT 'idx_tb00' PRIMARY KEY (a)); CREATE INDEX 'idx_tb01' ON tb (b); INSERT INTO tb (a, b) VALUES (1, '1'); INSERT INTO tb (a, b) VALUES (2, '2'); INSERT INTO tb (a, b) VALUES (3, '3'); INSERT INTO tb (a, b) VALUES (4, '4'); INSERT INTO tb (a, b) VALUES (5, '5'); INSERT INTO tb (a, b) VALUES (6, '6'); look that example and take a look in the result of "EXPLAIN QUERY PLAN" in booth order are the same index used! EXPLAIN QUERY PLAN SELECT * FROM tb ORDER BY a ASC; EXPLAIN QUERY PLAN SELECT * FROM tb ORDER BY a DESC; EXPLAIN QUERY PLAN SELECT * FROM tb ORDER BY b ASC; EXPLAIN QUERY PLAN SELECT * FROM tb ORDER BY b DESC; now take a look on EXPLAIN , only 2 operations are changed: Rewind(First) <=> Last, Next <=> Previous EXPLAIN SELECT * FROM tb ORDER BY b ASC; EXPLAIN SELECT * FROM tb ORDER BY b DESC; With this your time can be halved ! ----- "Paul Sanderson" <sandersonforens...@gmail.com> writed: > 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. > > 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. > > 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. > > Ill have a look at FTS as there are other benefits to using that. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
