Re: [sqlite] Extra Space Required for Index ?

[Simon Slavin]

On 30 Oct 2013, at 6:32pm, Raheel Gupta  wrote:

> I wanted to create an INDEX (not unique) of the md5sum column. Would the
> index also eat up 20 Bytes or more than that ?

At least as much space as the total contents of all the md5sum values.  
Assuming you did a VACUUM before you created the index.

But why are you asking us for a vague rule when you can find an exact figure 
yourself ?  Make a copy of your database, create the index, and compare the 
file sizes.  Then you know.

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

Re: [sqlite] Extra Space Required for Index ?

[Raheel Gupta]

>> B) By insisting on your email values being unique you have already
required SQLite to make up an index for the column. SQLite needs that index
so that when you do an INSERT it can quickly check to see that the value
you use doesn't already exist.
Agreed. I was just asking the general space requirements for indexing a
column.
So as mentioned in the earlier email, it is equivalent to the space of the
cell + a few additional bytes. Lets say I have a column with all rows
having a md5sum.
I wanted to create an INDEX (not unique) of the md5sum column. Would the
index also eat up 20 Bytes or more than that ?



On Wed, Oct 30, 2013 at 3:15 PM, Simon Slavin  wrote:

>
> On 30 Oct 2013, at 8:37am, Raheel Gupta  wrote:
>
> > email VARCHAR(255) UNIQUE NOT NULL
> > }
> >
> > I wanted to know if I create an INDEX for the column "email" what isg
> going
> > to be the extra space the index will occupy ?
>
> Two things:
>
> A) SQLite interprets "VARCHAR(255)" as "TEXT".  Each value does not take
> up 255 bytes of space.  The amount of space taken up varies with the length
> of the value.
>
> B) By insisting on your email values being unique you have already
> required SQLite to make up an index for the column.  SQLite needs that
> index so that when you do an INSERT it can quickly check to see that the
> value you use doesn't already exist.
>
> So creating another index on the same thing would just be a waste of space.
>
> Simon.
> ___
> 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] Extra Space Required for Index ?

[Simon Slavin]

On 30 Oct 2013, at 8:37am, Raheel Gupta  wrote:

> email VARCHAR(255) UNIQUE NOT NULL
> }
> 
> I wanted to know if I create an INDEX for the column "email" what isg going
> to be the extra space the index will occupy ?

Two things:

A) SQLite interprets "VARCHAR(255)" as "TEXT".  Each value does not take up 255 
bytes of space.  The amount of space taken up varies with the length of the 
value.

B) By insisting on your email values being unique you have already required 
SQLite to make up an index for the column.  SQLite needs that index so that 
when you do an INSERT it can quickly check to see that the value you use 
doesn't already exist.

So creating another index on the same thing would just be a waste of space.

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

Re: [sqlite] Extra Space Required for Index ?

[Clemens Ladisch]

Raheel Gupta wrote:
> CREATE TABLE users (
> uid INTEGER PRIMARY KEY AUTOINCREMENT,
> username VARCHAR(100) UNIQUE NOT NULL DEFAULT '',
> email VARCHAR(255) UNIQUE NOT NULL
>
> I wanted to know if I create an INDEX for the column "email" what isg going
> to be the extra space the index will occupy ?

About the same space that would be needed for a table with only the
email column, that is, the actual size of all email values, plus a few
bytes for each record.


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

Re: [sqlite] Extra Space Required for Index ?

[Stephan Beal]

On Wed, Oct 30, 2013 at 9:37 AM, Raheel Gupta  wrote:

> I tried to search for any docs on this but couldnt.
> This table is going to have 1 Million records and I need to save space
> here.
>

Any any all storage-related requirements, with the possible exception of
the page size[1], are internal details clients cannot rely upon. Any answer
you'll get here regarding internal details may be invalidated next week,
next month, or even in the next commit made to the sqlite tree.


> Adding an index makes the lookup very fast.
>

That's a classical case of a trade-off between space and time, and in the
general case more of one automatically implies less of the other.

[1] http://www.sqlite.org/pragma.html#pragma_page_size

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users