I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have further inventory table with reference to dictionary table. I have not worked earlier with such huge database.
InnoDB is good for maintaining relational database. There are category and subcategory references, so i am going to use InnoDB. There will come a situation where I need to show total count based on category or subcategory, or even based on state and city. So on... it may any combination.
I am familiar with creating indexes on most searched columns. I have heard about table partitioning also helps to speed up queries.
My Questions is what points should I consider at this early stage when creating such database tables which are going to have billions of rows, so that later on when tables grow larger, I can keep table performance at high level with select queries and DML quires (insert, update).
Guidance will help me a lot.