MySQL Forums
Forum List  »  InnoDB

How to optimize very large narrow tables?
Posted by: Sam Tse
Date: July 25, 2008 11:02AM

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)
) ENGINE=InnoDB

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!
Reply With Quote

Options: ReplyQuote


Subject
Views
Written By
Posted
How to optimize very large narrow tables?
2971
July 25, 2008 11:02AM


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.