Need Suggestion for Large Database

[sanhita]

Hi Everyone,

I really need a suggestion from you. I need to store more than 500 million records in a single table. These are basically historical data of different stock market for last 40 years.

Initially I thought about partitioning for each company. But there are more than 1024 companies. At the same time if I partition the database based on year then to show the chart for a particular company I need to call almost all the partitions.

I am little bit confused what should I do. Can any one help me in this regards?

Thanks,
Sanhita Basak

[shprung]

Try to create 26 tables or some small amount of tables using a simple hash. for example if the id is always few letters, use the first letter in the table name. If it is a number, you can use a simple mod (’%’) to get 100 tables. In that case, the 500 milion become about 1 million (of course this is assume even distribution which never the case)

If you have to store the 500million in one table, make sure all field in this one are integer (or mediumint) some of this field can point to another table/s with more info about each record.

for me, I have good result with upto 40million record and no luck with much more, so 500 in one table is pushing it…