A lot of rows for a very specific task
Posted by: lopo grio
Date: January 13, 2009 03:14PM

Dear mysql experts,

I need your advice in the design of a database.
I need mysql to hold a very simple table.
The table has 3 columns: a,b,c. a is a 64 bit integer, b and c are regular integers. a is not unique so it can't be a primary key.
This table will hold ~1,000,000,000 records.
Once all the data is uploaded, this data will not change - meaning there will be no UPDATEs on the table, only SELECTs.
Further more, the SELECTs will be filtered by column a (i.e. SELECT b,c FROM table where a=146781632). No LIKEs, no greater/lesser then, only equals.
Each application transaction will make ~10000 select queries on this billion records table, and it should complete in no more then several seconds.

My questions are:
1. What engine shuold I choose? (INNODB? MyISAM?)
2. How should I index this table? obviously there should be an index on a, but is creating a simple BTREE index is the right way? another consideration here is the (a,c) should be unique.
3. What do you think is the minimal hardware that can handle such a task? is 32 bit linux OS good enough? Must I use a cluster?
4. DB cache of queries could play an important role here. What is the maximal memory limitation if any?

I could use any idea u might have...

Options: ReplyQuote

Written By
A lot of rows for a very specific task
January 13, 2009 03:14PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.