Re: [sqlite] VARCHAR or TEXT to save sapce

[Richard Hipp]

On 9/2/16, Dominique Devienne  wrote:
>
> Does ANALYZE gather actual statistics on lengths?

The sqlite_stat1 table has a place to put that information and SQLite
will use the information if it is present, but the ANALYZE command
does not currently generate it.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VARCHAR or TEXT to save sapce

[Dominique Devienne]

On Fri, Sep 2, 2016 at 5:51 PM, Richard Hipp  wrote:

> On 9/2/16, Dominique Devienne  wrote:
> >
> > For example, we use Guid extensively, which are typed blob, but often
> > add that CHECK(length(guid) = 16) constraint. I know 20 is not far off
> > from 16 in this particular case, but more accurate is always better, no?
>
> The difference between 16 and 20 won't matter here.  What you have is fine.
>
> 16 versus 1600 might matter a little.  16 versus 20 will not.


Thanks. I take that as a no to my "will it use length-check-constraint"
question :).

Does ANALYZE gather actual statistics on lengths?

Meaning that the datatype length heuristic you described is used only when
there are no stats available, right?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VARCHAR or TEXT to save sapce

[Richard Hipp]

On 9/2/16, Dominique Devienne  wrote:
>
> For example, we use Guid extensively, which are typed blob, but often
> add that CHECK(length(guid) = 16) constraint. I know 20 is not far off
> from 16 in this particular case, but more accurate is always better, no?

The difference between 16 and 20 won't matter here.  What you have is fine.

16 versus 1600 might matter a little.  16 versus 20 will not.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VARCHAR or TEXT to save sapce

[Dominique Devienne]

On Fri, Sep 2, 2016 at 5:04 PM, Richard Hipp  wrote:

> On Fri, 02 Sep 2016 15:10 +0100, Tim Streater 
> wrote:
> > On 02 Sep 2016 at 15:05, Dave Blake  wrote:
> >
> >> Can I confirm that defining feilds as VARCHAR(20) rather than TEXT, if I
> >> know I only want 20 chars or less,  will result in a smaller database?
> >
> > Makes no difference whatever.
>
> Mr. Streater's answer to Mr. Blake's question is correct:  TEXT vs
> VARCHAR(20) make no difference at all in the file format.
>
> But for completeness, let me just point out that there is a difference
> between these two declarations from the point of view of the query
> planner.  When the query planner sees VARCHAR(20) versus (say)
> VARCHAR(2000) it assumes that the second field will usually store
> about 100 times as much text as the first.  In other words, it uses
> the argument to VARCHAR as an estimate for the average size of the
> field.  This goes into building an estimate for the average size of
> each row.  When trying to decide between two indexes, if all else is
> equal, SQLite will choose the index with the smaller estimated row
> size.
>
> So, while the declared type makes very little difference, and though
> it makes no difference at all with regard to how content is stored on
> disk, the declared type is not totally ignored by SQLite and does have
> some small influence on query planning.  Note that the difference is
> quite small and it takes a subtle experiment (and a subtle test case)
> to verify that the size estimation logic is working.
>
> For size estimating purposes, SQLite treats "TEXT" as if it were
> "VARCHAR(20)".  https://www.sqlite.org/src/artifact/c2ccfcd?ln=1195


Very interesting. Thanks for explaining this Richard. I had no idea.

Any chance a CHECK constraint of the form CHECK(length(col) = N)
would be recognized similarly for size estimates?

For example, we use Guid extensively, which are typed blob, but often
add that CHECK(length(guid) = 16) constraint. I know 20 is not far off
from 16 in this particular case, but more accurate is always better, no?

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VARCHAR or TEXT to save sapce

[Richard Hipp]

On Fri, 02 Sep 2016 15:10 +0100, Tim Streater  wrote:
> On 02 Sep 2016 at 15:05, Dave Blake  wrote:
>
>> Can I confirm that defining feilds as VARCHAR(20) rather than TEXT, if I
>> know I only want 20 chars or less,  will result in a smaller database?
>
> Makes no difference whatever.

Mr. Streater's answer to Mr. Blake's question is correct:  TEXT vs
VARCHAR(20) make no difference at all in the file format.

But for completeness, let me just point out that there is a difference
between these two declarations from the point of view of the query
planner.  When the query planner sees VARCHAR(20) versus (say)
VARCHAR(2000) it assumes that the second field will usually store
about 100 times as much text as the first.  In other words, it uses
the argument to VARCHAR as an estimate for the average size of the
field.  This goes into building an estimate for the average size of
each row.  When trying to decide between two indexes, if all else is
equal, SQLite will choose the index with the smaller estimated row
size.

So, while the declared type makes very little difference, and though
it makes no difference at all with regard to how content is stored on
disk, the declared type is not totally ignored by SQLite and does have
some small influence on query planning.  Note that the difference is
quite small and it takes a subtle experiment (and a subtle test case)
to verify that the size estimation logic is working.

For size estimating purposes, SQLite treats "TEXT" as if it were
"VARCHAR(20)".  https://www.sqlite.org/src/artifact/c2ccfcd?ln=1195

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VARCHAR or TEXT to save sapce

[R Smith]

On 2016/09/02 4:05 PM, Dave Blake wrote:

Can I confirm that defining feilds as VARCHAR(20) rather than TEXT, if I
know I only want 20 chars or less,  will result in a smaller database?


In addition to the other useful replies, you might actually be 
interested in ensuring the text field in question won't have more than 
20 characters of data, which you can achieve in 2 ways:


First, by adding a CHECK constraint:
https://www.sqlite.org/lang_createtable.html#ckconst

Perhaps something like: "SomeField" TEXT CHECK(LEN("SomeField") <= 20)
This will ensure the length is not higher than 20 but will FAIL the 
INSERT/UPDATE if you try to upload a value with more than 20 Chars.


Another option is a TRIGGER on UPDATE or INSERT that might limit the 
value in that column to 20 chars. This will not fail, but may just 
truncate the value.
Note: This is a really bad thing to do in general DB design guidelines, 
but your case might be special.

https://www.sqlite.org/lang_createtrigger.html


Cheers,
Ryan

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

Re: [sqlite] VARCHAR or TEXT to save sapce

[Olivier Mascia]

> Le 2 sept. 2016 à 16:05, Dave Blake  a écrit :
> 
> Can I confirm that defining feilds as VARCHAR(20) rather than TEXT, if I
> know I only want 20 chars or less,  will result in a smaller database?

That won't change anything. The (20) in VARCHAR(20) is purely 'documentation' 
for you, and insignificant to SQLite.  Which is very different to what you 
might find in other SQL engines.  The storage will be as small as possible, 
dynamic and identical in both cases.  Neither VARCHAR(20) nor TEXT will store 
anything more than required to store the length of the string and the string 
itself, as short or as long it is.  The length encoding itself is varying in 
size and won't cost you needless bytes for small lengths.

See https://sqlite.org/fileformat2.html and more specifically: 
https://sqlite.org/fileformat2.html#section_2_1

This https://sqlite.org/datatype3.html will tell you all there is to know about 
the datatypes in SQLite.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



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

Re: [sqlite] VARCHAR or TEXT to save sapce

[Tim Streater]

On 02 Sep 2016 at 15:05, Dave Blake  wrote: 

> Can I confirm that defining feilds as VARCHAR(20) rather than TEXT, if I
> know I only want 20 chars or less,  will result in a smaller database?

Makes no difference whatever.

See:




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

[sqlite] VARCHAR or TEXT to save sapce

[Dave Blake]

Can I confirm that defining feilds as VARCHAR(20) rather than TEXT, if I
know I only want 20 chars or less,  will result in a smaller database?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users