Geek asks and answers

Organization and optimization of the MySQL table (Rails)

I have been learning Ruby on Rails in the last few months without any prior programming experience. Recently, I have been thinking about database optimization and table organization. I know there are great books on this subject, but I usually participate in this example / along the way.

Here's a hypothetical situation:

Let's say I'm creating a social network for a niche community with 250,000 members (users). Users can attend events. Say 50,000 past / present / future events. Like Facebook events, a user can attend any number of events, and an event can have any number of participants.

The database will have a table for users and an event table. Somehow I would have to create a connection between users and events. I could create an “events” column in the user table so that each user row contains a hash of event identifiers, or I could create a “visitors” column in the event table so that each event row contains a hash of user identifiers,

None of these solutions seem perfect. On the user profile page, I want to display a list of events with which they are associated, which will require scanning of 50,000 event lines for the user ID of the specified user if I include the "Participants" column in the event table. Similarly, on the events page, I want to display a list of participants for the event, which will require scanning 250,000 user lines for the event identifier of the specified event, if I include the "events" column in the users table.

Option 3 will be to create a third table containing the participant information for each event, but I don’t see how this can solve any problems.

? Rails , , . , // .. , 250 000 - 10 ? 100? 1,000ms? , . ?

+3
3

" " .

(, UserEvent UserAttendsEvent Attends), , .

, , "" "".

2 , , , .

UserEvent , , , , , , ..

, "Attends". (userID), (eventID), , .. , Event.

, , . , , ( ) " U2 , 2011" , .

database plan:
1. use eventTitle index in table Event 
     to find that the event has id 47519 
   (good for us that we have created such an index).
2. use eventID index in table Attends 
     to find all (469) userids that have attended eventid 47519.
3. use the userid index in table User
     to find all the info of the 469 users.
4. search the info (birthdate) from those
     to keep only those (3) that have birthday July 24th.
     (we have not created any index that can be used here)

, . .

, - db , . "" ( ), .

+3

" , , 50 000 , " " ."

50 000 , , , , . , . : , .

, .

-

0

, , .

In Rails, this is handled using the has_and_belongs_to_many clause in the model, which creates a table for you with many solutions for many. Documentation here: http://guides.rubyonrails.org/association_basics.html

With the help of the permission object, the database does not actually look at the whole table looking for information, it uses the index of the b tree to find the specific rows that are needed - therefore, many, many do a direct fix to the performance problem that you are raising.

0
source

More articles:


All Articles