Best configuration for a MySQL database with 10M users?

Question

I have a prototype database with two tables. They get joined in a series of six queries. One table has about 250K records, and the other about 100K. My question is, if I want to multiply this times 1000x or even 10M concurrent users each with their own set of 250 and 100K records, should I split each user's data into separate tables, separate databases or just keep everyone's data in the same two tables? Which would be the fastest to execute a query on?

Thank you for your response.

Answer 1

assuming your FirstTable is 10M records and your SecondTable is 10M records .



splitting the tables into several tables, eg. FirstTable_1, FirstTable_2, FirstTable_3 and SecondTable_1, SecondTable_2 etc can make your query run faster.

but the disadvantages of this technique is :

You need additional query to find the right tables, assuming you split the tables based on date field , first char from some fields, male and female records etc ,

you need to build union query if you want to search some records from all Tables , and the performance will be drop if you make a join from all First_Table into all Second_Table.

Answer 2

The fastest is to separate out the users table and join on the other tables. Get the userid and query or join the other tables based on that. For example:



tblUsers

UserId, Password, Username



tblPreferences

PreferenceID, UserId



tblLogins,

LoginId, UserId

Answer 3

You should just put all the data into the same tables, but should normally have a userid field to identify the owner of each entry. This way you can run filters to control who can see which entries, and people can see just their own data if they want to.

Answer 4

Ideally you would put the columns you have in the tables and a description on what the tables are used for. Would be easier for you to get an answer.