Table wirth several billion entries

[Peter Jefferson]

Hello,
I have a generic data model (basically 2 tables) which allows to express very complex graph structures for research issues. The idea for this design is, that the data model has not be be changed if new structure types have to be represented.

Problem is- can I pump a billion or more entries into one InndoDB table and still hope to perform selects on it? Right now it takes about 1.4ms per insert (using jdbc and batch execution). I started a test run over the weekend to insert a tree-structure. Right now there are 34M entries and a select count(KEYID) from TABLE takes 15 secs. Ok, these results suggest that it is too heavy ;-) but maybe I just have a bad setup. Iam still new to database handling.

I use the standard configuration "heavy inndodb 4gb" which is shipped with the mysql sources. I have a 4 processor AMD64, 8gb linux box. I've changed the setting for table-memory from 2gb to 6 gb. There is one disc with raid1. Is a multiprocessor system of any use if mostly only one user makes queries on the db?

Maybe it is possible to physically split up a table and logically still operate on one db? Might the clustering be of any use?

Thanks for any suggestions and help you can give!

Best regards,

Peter