I think the idea was to
SELECT md5_str FROM <table> WHERE rowid = ?;
instead of
SELECT md5('mm' || ?);
or even
SELECT rowid FROM <table> WHERE md5_str = ?;
which is a fast way of finding the inverse function for md5.
-----Ursprüngliche Nachricht-----
Von: RSmith [mailto:[email protected]]
Gesendet: Montag, 17. März 2014 14:39
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Error "Database or disk if full" shown when indexing for
2 billion records.
That's insane... well done :)
To create the Index you will need at least as much disk space as already
used... so you will need another 87GB (approx), not just another 50GB.
And it will take no longer to create the table + Index from the start than it
will take to create first the table, then the index - plus you will need less
overall disk-space if you do it from the start, but much more than 87GB... try
making the table without the Rowid.. that should save you a lot:
CREATE TABLE t (str_md5 VARCHAR(32) PRIMARY KEY COLLATE NOCASE) WITHOUT ROWID;
That should work the best for md5 hashes and might even get to under your GB
total space (including the key).
What I would do is create that table, then add only 1 million entries indexed
and all... see the total size, multiply it with the amount of entries you need
in total, as a rough guide to how much you can put in the space.
HTH!
Ryan
On 2014/03/17 15:32, Yi Wang wrote:
> I inserted 2 billion records with only 1 column with name of
> "str_md5", the value is the MD5 encrypted value of "mm"+rowid(such like
> MD5(mm121212...).
>
> I didn't not create primary key for the only column b/c i am not sure
> it would slow the speed of insert command. The whole insert of 2
> billion records took me over 18hours.
>
> But when I hope to index the column with the sql command of
>
> ”create index tableMD5_idx on tableMD5(on str_md5);"
>
> The index command ran for around 1 hour and then the error "Database
> or disk if full" shown up.
>
> FYI: The sqlite file only contains that only 1 table with 1 column.
> The size of the DB file took around 87G disk space.
>
> And the disp space is still 50G more free space to reach full. So I am
> not sure whether it's the space problom b/c according to my previous
> experience, the index command would increase around 1/3 size of the
> currnet DB file (I took a test DB with one hundres million records
> which took abour 7G space, and after the index command the final size
> reach to 9G around.)
>
> So any ideas? Thanks in advance.
>
> Yi.
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-----------------------------------------------------------------------
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: [email protected]
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
