For your example create a separate table with just the first letter and build
an index on it.
create table text(t string);
create table first(textid int, first_char char);
create trigger makefirst after insert on text
begin
insert into first values(new.rowid,substr(new.t,1,1));
end;
insert into text('a_one');
insert into text('b_two');
explain query plan select * from text where rowid in (select textid from first
where first_char='a');
sele order from deta
---- ------------- ---- ----
0 0 0 SEARCH TABLE text USING INTEGER PRIMARY KEY
(rowid=?) (~25 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 0 SEARCH TABLE first USING INDEX first_index (l=?)
(~10 rows)
select * from text where rowid in (select textid from first where
first_char='a');
t
----
a_one
select * from text where rowid in (select textid from first where
first_char='b');
t
----
b_two
Note that this doesn't have a complete trigger package for updates and deletes
but you are describing a static set where you don't need them.
Without this you would end up doing this:
sqlite> create index textindex on text(t); <<<< this is actually useless but
we'll show it anyways just to prove it doesn't work
sqlite> explain query plan select * from text where substr(t,1,1)='a';
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE text (~500000 rows)
sqlite> explain query plan select * from text where t like 'a%';
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE text (~500000 rows)
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: [email protected] [[email protected]] on
behalf of Mohit Sindhwani [[email protected]]
Sent: Sunday, October 09, 2011 5:15 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Can pre-sorted data help?
Thanks Richard and Petite,
On 9/10/2011 1:05 AM, Richard Hipp wrote:
> On Fri, Oct 7, 2011 at 12:30 PM, Mohit Sindhwani<[email protected]> wrote:
>
>> Hi All,
>>
>> I have been trying to see how we can make one of our databases more space
>> efficient.
>
> Have you tried increasing the page size? You seem to currently be using
> 1024-byte pages. Try increasing that to 4096 or 8192. You might get both a
> performance increase and a database size reduction.
>
> PRAGMA page_size=8192; VACUUM;
>
> The VACUUM might take a little while on your database,.
Richard: The page size is indeed changed to 8KB in one of the final
steps before we pack it up with CEROD - and it does help with the
database size!
Petite: thanks for pointing that out - we'll drop the index on the
primary key!
Both these suggestions will help.
I'm still trying to see if we make use of the fact that some data can be
sorted... I can't find the best example to illustrate the need for it..
Let me see if I can find something that explains it better. As an
example, if we had something like
create table titles (id integer primary key, title text, ...);
could we sort the records by title and use that in some way to restrict
the search space when searching titles starting with a specific letter?
Thanks,
Mohit.
9/10/2011 | 6:13 PM.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
