MySQL Performance: MySQL => One Very Large Table vs Many Little Tables

One Very Large Table vs Many Little Tables [message #1508]

Mon, 02 July 2007 11:45

guaoguao
Messages: 2
Registered: July 2007

Junior Member

Hi everyone!

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 Smile

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:

1. List all records created by user_875
2. 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!

Report message to a moderator