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!