Huge list of words, SQLite

Solar2D General Questions/Discussion
1

I try to load a huge list of words (over 1,3 million). I have some smaller lists of couple hundred thousands (other languages). Those I can load in a lua table without problems. However the nice German word list of http://germandict.sourceforge.net/  is 18.3MB as a text file and seems to be to large. So I am thinking to put that in a SQLite database. I have do have experience with MySQL but not with SQLlite. 

Some questions.

The SQLite database will not get loaded into system memory (so memory will not get overflooded)?

Any tips on performance / setting it up?

thx

2

My currently released game (Spell Them Out) has an English database of 32,000 words and takes up about 800K in SQLite,

My suggestions:

  • Download SQLite Database Browser.  Great tool, and free!  You can import a text file to create a database quickly, and you can execute commands to see if they work as well.
  • No, the database will not be loaded in memory… only the results of the SQL calls will be loaded into a table in memory.
  • I followed this page to set everything up, and performance is quite good.

Hope this helps!

–John

3

Hey John,

Thx a lot for your advice, gonna try it tomorrow.  :slight_smile:

gRTz Jack

4

I’m using sqlite to house my 120,000+ terms and definitions. No hiccups either, like John said, get a good tool to help you with getting the data in the database and run queries with and you’ll be fine. Corona makes it easy to interact with the DB.

5

Well the SQLite Database Browser was slow when importing lots of records from CSV, it would take hours. So I just used the sqlite3 command in the terminal of OSX which took only seconds. Its pretty easy, explained here http://stackoverflow.com/questions/3260825/simple-sql-lite-table-import-question

Afterwards I ran a ‘Vacuum’ command to clear the database of the space of the deleted temp tables.

6

Jack,

Yes, the import of data using the SQLite Browser is somewhat slow… I do remember that when I did it a while ago.

Luckily, I only had to do that 1 time.

The browser and SQL command browser is very useful.

7

I use Valentina Studio or the FireFox plugin SQLite Manager

8

My currently released game (Spell Them Out) has an English database of 32,000 words and takes up about 800K in SQLite,

My suggestions:

  • Download SQLite Database Browser.  Great tool, and free!  You can import a text file to create a database quickly, and you can execute commands to see if they work as well.
  • No, the database will not be loaded in memory… only the results of the SQL calls will be loaded into a table in memory.
  • I followed this page to set everything up, and performance is quite good.

Hope this helps!

–John

9

Hey John,

Thx a lot for your advice, gonna try it tomorrow.  :slight_smile:

gRTz Jack

10

I’m using sqlite to house my 120,000+ terms and definitions. No hiccups either, like John said, get a good tool to help you with getting the data in the database and run queries with and you’ll be fine. Corona makes it easy to interact with the DB.

11

Well the SQLite Database Browser was slow when importing lots of records from CSV, it would take hours. So I just used the sqlite3 command in the terminal of OSX which took only seconds. Its pretty easy, explained here http://stackoverflow.com/questions/3260825/simple-sql-lite-table-import-question

Afterwards I ran a ‘Vacuum’ command to clear the database of the space of the deleted temp tables.

12

Jack,

Yes, the import of data using the SQLite Browser is somewhat slow… I do remember that when I did it a while ago.

Luckily, I only had to do that 1 time.

The browser and SQL command browser is very useful.

13

I use Valentina Studio or the FireFox plugin SQLite Manager