Fast single table database.

Question

I have an analytics database where I make complex queries. Each of these queries generates thousands of rows. I want to store these results in some kind of on disk cache so I can get the results later on. I can't insert the results back into the database where the results came from as that database is read only. The requirements of this cache are.

• Very very fast to insert rows. MySQL is a non-starter.
• Fast to filter and sort the results
• No multi-table joins required
• No transactions

I'm interested to hear of any SQL or NoSQL solutions that can help with this.

Answer 1

Have you looked at SQLite? It's used by apps ( i.e. Thunderbird, FF ) when they need query power, but not a full-fledged relational database, amongst other uses.

Answer 2

You should probably take a look at NoSQL solutions. Maybe Cassandra, http://cassandra.apache.org/ which is used by facebook and digg among others

Answer 3

If you have lots of RAM, might want to try the SQLite in-memory database: http://www.sqlite.org/inmemorydb.html

With less RAM, try SQLite temporary databases (further down the page - see hyperlink above). These also use the in-memory cache but parts might be flushed to disk if the database becomes large.

Answer 4

Had you considered just writing the rows to files? Log at high speed, analyze at leisure.

Answer 5

You should also take a look at Advantage database. The local server is free and it supports both SQL and non-SQL solutions. The non-SQL solution will certainly be faster than the SQL approach. The table can be designated as cached so it will stay in memory as long as possible for fast insertion. Advantage Database Server

Answer 6

You didn't specify the platform, but on Windows you can use the built-in Esent database engine. It would provide data persistence with sorting and high performance.