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...
Thanks!