UNIX for Advanced & Expert Users

relational databases handled sorted data and range based queries very nicely if the structures are designed well. For large amounts(hundreds of gigabytes or many terabytes of data you can partition the data by range and or by a key. I have not done this with MySQL but I know it works very well with DB2.

Check this out. It does not look difficult to implement in MySQL. I think it's worth a try since your already familiar with MySQL.
MySQL :: MySQL 5.1 Reference Manual :: 18.2 Partitioning Types

The kornshell 93 is capable to seek in a file for some time now. Here is an example:

First a simple file. Every line is 7 bytes long (including the newline character):

Code:

line 1
line 2
line 3
line 4
line 5

Here we go:

Code:

$ read VAR < file <# ((14))
$ print $VAR
line 3
$ read VAR < file <# ((7))
$ print $VAR
line 2

Or, if you do not want to reopen the file every time:

Code:

$ exec 3<file
$ read -u3 VAR 3<# ((21))
$ print $VAR
line 4
$ read -u3 VAR 3<# ((7))
$ print $VAR
line 2

Oops, just saw, that fpmurphy said that already, sorry for double post.

It's fine, more examples are always good!

That's interesting, file seeking was the one thing I always lacking in bash scripts... I wrote a little wrapper library for fopen/fclose/fscanf calls, but just couldn't do a real fseek in bash.

Data sorted on primary key is ideal for bulk loading into mainstream databases. Though indexes take space the data retrieval performance should be good.

The nicest key for bulk loaded data is time, append-only table. For interactive, it is better to mix it up (user, time) so all users are not fighting to insert to the same page, but you can stage it clustered one way and batch move it in near real time background.

No db beats the storage density of flat files, never mind compressed. That is why I suggested zip/unzip and file/dir partitioning. You can ask unzip to pick up 50 small compressed files by wild card path and write all the data uncompressed to stdout on a pipe to your app., which can be HXTT JDBC or the like. If you partition on several keys, you can search key by file name within zip archives for each day, week, month or year. With modern CPU speed, it may beat formal RDBMS not only on space but on speed!