How to optimize large narrow tables?

[ursixc9]

Hi,
I have a very large and somewhat narrow table which needs to be accessed constantly by the application. The table looks as follow:
CREATE TABLE SEQUENCE
SEQUENCEID INT(11),
NAME VARCHAR(255),
PARENT INT(11),
ORDER TINYINT(4),
END TINYINT(4),
PRIMARY KEY (ID),
INDEX `nameParent' (NAME, PARENT)

What happens is the Sequence table will be called recursively through a function and the `parent` field is used to reference another SequenceID row from the same table. So by finding a specific name from the table, the application can keep tracing the `parent` field of different rows recursively until the 'end' field specify that the sequence has ended. This has been working fine with 1 million records but i need to be able to scale this to 100 billion records. I was thinking of using memcache to speed up the process but I think memcache will only be able to do so much if the original table design is not optimized to begin with. So my question is what methods are available for me to optimize my table design given that this table will be accessed 2-3000 times a second. Any help would be greatly appreciated. Thanks in advanced!

[skhanal]

to speed up one suggestion is to separate tables into two so that hierarchical table only has parent child records and do id to name translation on second table.

by the way what database system are you using?

[ursixc9]

Thanks for your reply. I thought my question is dead in the water when no one replied for over 3 days. I am using MySQL and i want to scale this table horizontally into many different databases. I like you suggestion, it make sense to split the sequenceid and name into one table then have another parent lookup table that reference the sequenceid.

But here's another idea I have and i'm not sure if it make sense at this moment. I want to hash the name field [i.e. md5(name)] and then use the first 2 hex character to split the tables. For instance 00 will be one table FF will be another so that my query will know exactly which database to go to for executing the query.... what do you think? Another thing is what should i use as the sequenceid... UUID or BigInt?? Thanks for your help in advance!

[skhanal]

I am not familiar with mySQL but don't make it bigger than you actually need. Is bigint 8 bytes?. if yes then that should be sufficient for your requirement, UUID I guess is like GUID which is 16 bytes.