[sqlite] Sqlite3 Optimization Question

[Michael Thomason]

I am doing mobile development, and the size of my application is
getting too large for comfort.  I'd like to find a way to reduce the
file size, but maintain the performance.

I have a database that is read only.  It is ordered and each row is
unique.  It has only one table, which is significantly large.

CREATE TABLE table (column TEXT PRIMARY KEY);

The only query that ever runs against it is as follows:

SELECT count(*) FROM table WHERE column = '%q'

It works fine, it's easy, and it's fast.

The only problem is that the table is very large.  If I list out the
contents of the table in a text file, it is one-third the size of the
database.  So, if the text file is 12 MB, the database is 32 MB.

I get the feeling that the database doesn't need to be that large.  It
seems like that file size is the table size plus two index sizes.  All
this, when I only really need just an index to tell if the record
exist.  I never update the record.

Again, what I have now works fine; it's just a large file.

Do you know of a better way to do this in Sqlite3, or a strategy or
algorithm to do it from text or some data structure?

Cheers!
Michael
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 Optimization Question

[Martin Engelschalk]

Hi,

perhaps you could drop the primary key. The query you mentioned,
SELECT count(*) FROM table WHERE column = '%q'
does not utilize it, and if you do execute queries which do, do not 
update the db, and have no other tables, then the primary key serves no 
function.

Martin

Michael Thomason wrote:
> I am doing mobile development, and the size of my application is
> getting too large for comfort.  I'd like to find a way to reduce the
> file size, but maintain the performance.
>
> I have a database that is read only.  It is ordered and each row is
> unique.  It has only one table, which is significantly large.
>
> CREATE TABLE table (column TEXT PRIMARY KEY);
>
> The only query that ever runs against it is as follows:
>
> SELECT count(*) FROM table WHERE column = '%q'
>
> It works fine, it's easy, and it's fast.
>
> The only problem is that the table is very large.  If I list out the
> contents of the table in a text file, it is one-third the size of the
> database.  So, if the text file is 12 MB, the database is 32 MB.
>
> I get the feeling that the database doesn't need to be that large.  It
> seems like that file size is the table size plus two index sizes.  All
> this, when I only really need just an index to tell if the record
> exist.  I never update the record.
>
> Again, what I have now works fine; it's just a large file.
>
> Do you know of a better way to do this in Sqlite3, or a strategy or
> algorithm to do it from text or some data structure?
>
> Cheers!
> Michael
> ___
> 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

Re: [sqlite] Sqlite3 Optimization Question

[Michael Thomason]

Thank you.  I like that answer and I'll give it a try.

The application is a word game, and I have a dictionary of words.  The
user enters a word, and the application checks against that word to
see if it exist.  That's the only query ever used.

Would the following be any better?

SELECT rowid FROM table WHERE column = '%q' LIMIT 1

Best regards,
Michael

On Fri, Jan 22, 2010 at 9:15 AM, Martin Engelschalk
 wrote:
> Hi,
>
> perhaps you could drop the primary key. The query you mentioned,
> SELECT count(*) FROM table WHERE column = '%q'
> does not utilize it, and if you do execute queries which do, do not
> update the db, and have no other tables, then the primary key serves no
> function.
>
> Martin
>
> Michael Thomason wrote:
>> I am doing mobile development, and the size of my application is
>> getting too large for comfort.  I'd like to find a way to reduce the
>> file size, but maintain the performance.
>>
>> I have a database that is read only.  It is ordered and each row is
>> unique.  It has only one table, which is significantly large.
>>
>> CREATE TABLE table (column TEXT PRIMARY KEY);
>>
>> The only query that ever runs against it is as follows:
>>
>> SELECT count(*) FROM table WHERE column = '%q'
>>
>> It works fine, it's easy, and it's fast.
>>
>> The only problem is that the table is very large.  If I list out the
>> contents of the table in a text file, it is one-third the size of the
>> database.  So, if the text file is 12 MB, the database is 32 MB.
>>
>> I get the feeling that the database doesn't need to be that large.  It
>> seems like that file size is the table size plus two index sizes.  All
>> this, when I only really need just an index to tell if the record
>> exist.  I never update the record.
>>
>> Again, what I have now works fine; it's just a large file.
>>
>> Do you know of a better way to do this in Sqlite3, or a strategy or
>> algorithm to do it from text or some data structure?
>>
>> Cheers!
>> Michael
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 Optimization Question

[ve3meo]

Uhh, sounds like a search function of a text editor or a simple routine you 
could write (or find) in your development language. I can't see the magic of 
using sqlite.

Tom

"Michael Thomason"  wrote 
in message 
news:3cbb39411001220619j1c6cc8f5x46e0aeb4d0b91...@mail.gmail.com...
Thank you.  I like that answer and I'll give it a try.

The application is a word game, and I have a dictionary of words.  The
user enters a word, and the application checks against that word to
see if it exist.  That's the only query ever used.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 Optimization Question

[Igor Tandetnik]

Martin Engelschalk wrote:
> perhaps you could drop the primary key. The query you mentioned,
> SELECT count(*) FROM table WHERE column = '%q'
> does not utilize it

Does too. Primary key provides the index for the search. Without it, SQLite 
would do full table scan. Use EXPLAIN QUERY PLAN to convince yourself.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 Optimization Question

[Michael Thomason]

>
> Primary key provides the index for the search. Without it, SQLite would do 
> full table scan. Use EXPLAIN QUERY PLAN to convince yourself.

I could use a binary search, but I worry about having a ten MB array
in memory on a mobile device, though it should work fine.

>Uhh, sounds like a search function of a text editor or a simple routine
>you could write (or find) in your development language. I can't see
>the magic of using sqlite.

The magic is that it's easy and it works.  The problem is solved.
But, in this case, all I need is the index.

Maybe I'll try a binary search, or something else along those lines.

>
> Igor Tandetnik
>
> ___
> 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

Re: [sqlite] Sqlite3 Optimization Question

[Max Vlasov]

> Maybe I'll try a binary search, or something else along those lines.
>

You can mix binary search with sqlite as a storar. The idea is to place your
words ordered by text and use rowid as indexes.

So after creating a new table

CREATE TABLE Table2 AS SELECT * FROM Table ORDER BY Text

you now can access each row by "index" since rowids is guaranteed to be
consequential numbers. So accessing in your binary search is

SELECT Text FROM Table2 WHERE rowid=?

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 Optimization Question

[Simon Slavin]

On 22 Jan 2010, at 2:19pm, Michael Thomason wrote:

> Thank you.  I like that answer and I'll give it a try.
> 
> The application is a word game, and I have a dictionary of words.  The
> user enters a word, and the application checks against that word to
> see if it exist.  That's the only query ever used.

For that particular use, I do not think that any SQL engine is going to help.  
You are better off scanning a text file even if your dictionary is a few 
megabytes in size.  The words as a text file will be smaller than any database 
could be, and the scanning (if correctly written and optimised) should not take 
longer (for a 12MB text file) than a user would expect to wait for their turn.  
You will need to pick a delimiting character, perhaps ',', and your file should 
look like this:

,mankind,ape,car,daffodil,

with the delimiters appearing at both the beginning and end.  When scanning the 
file you look for

,myword,

and all your routine needs to return is whether it was found or not.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 Optimization Question

[Michael Thomason]

>> Maybe I'll try a binary search, or something else along those lines.
>>
>
> You can mix binary search with sqlite as a storar. The idea is to place your
> words ordered by text and use rowid as indexes.
>

I tried this, and it works surprisingly well.

I'm also going to try a straight binary search form an array, just to
see how much memory my app then consumes.

Anyway, thanks for all the advice.

> So after creating a new table
>
> CREATE TABLE Table2 AS SELECT * FROM Table ORDER BY Text
>
> you now can access each row by "index" since rowids is guaranteed to be
> consequential numbers. So accessing in your binary search is
>
> SELECT Text FROM Table2 WHERE rowid=?
>
> Max
> ___
> 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

Re: [sqlite] Sqlite3 Optimization Question

[Jens Miltner]

Am 22.01.2010 um 15:01 schrieb Michael Thomason:

> The only problem is that the table is very large.  If I list out the
> contents of the table in a text file, it is one-third the size of the
> database.  So, if the text file is 12 MB, the database is 32 MB.

You may also want to check the encoding of your database: UTF-8  
storage may be more efficient, especially when you're expecting to  
have mostly English words (or words in a language using roman  
characters).
If your database uses UTF-16 encoding, this essentially doubles the  
storage space needed for those languages.

You can use "pragma encoding" to query and set the string encoding in  
your database (see )



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users