Speed is going to depend heavily on the disk(s).

Using SQLite, what do you call slow? Could you please mention your harddisk type with the achieved inserts per second?

PostgreSQL might be a option, even if it isn't as delightfully simple as SQLite is.

For bulk load postgres has COPY, and I used it on a version of your program: 

$ cat ./generate_words.pl
#!/bin/env perl
use strict;
my $numofwords= 50_000_000;
for (my $i=0; $i<$numofwords; ++$i)
{
        my @chars = ( "A" .. "Z", "a" .. "z", 0 .. 9, qw(! @ $ % ^ & *
+) );
        my $rin = join("", @chars[ map { rand @chars } ( 1 .. 5 ) ] ) 
+;
        print $rin, "\n";
}

[download]
$ time perl ./generate_words.pl > words.txt;

real    7m54.686s

$ time ( < words.txt psql -d test -c "
   drop table if exists words;
   create table words (word text, id serial);
   copy words(word) from stdin csv; " )

real    1m16.223s

[download]

So, generating the data took 8 minutes, loading as 50M rows just 1 minute, adding an index another 9 minutes (not shown) (on a raid10, 8 disks, I think; just SATA).

In reply to Re: fast simple DB (sqlite?) skeleton? (PostgreSQL) by erix
in thread fast simple DB (sqlite?) skeleton? by iaw4

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.