Sorry if this sounds like a repeat question, I’ve searched the forums and found some threads but nothing is quite what I wanted to ask. This is pretty important to me, so I just want to make sure I get this right )
I’ll Illustrate my question with an example:
Let’s say you have 100 million users registered on your site that can create “records.” By records I mean write some text, pick a date between 1 A.D to 2010 A.D, and save it. The problem is I need to be able to find records based on who created them, as well as by the time they were created. For example:
- List all records created by user_875
- List all records that are within 2 days of April 24th, 1983.
The most important thing is that the SELECTS are extremely fast. So I don’t think making one table for the records will work. Let’s say there are 100 SELECT queries (to find records) per second, and 5 INSERT queries (to create new records) per second.
I was thinking:
Create 1 Table for the 100 million users which stores user information as well as login/password info with an index on login/password.
Create ~104,000 Tables for the records, where each table stores
the records for a certain week. For example table_for_week_5_in_1564. This way to find records that are within a certain date range, I could just grab those tables which should be much faster than querying one gigantic table?
However I’m not sure what to do about quickly finding all the records posted by a certain user? I guess I could create one table for each user that lists the record time, and number (as a reference to one of the weekly record tables) so I could find them that way?
Sorry if this is a dumb question, I’m rather new at this. I have also read about table partitioning so I guess I could put all the records into one large table and partition it by week?
Any help is greatly appreciated, thanks in advance!