I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows (either in a single table or combined with other tables).
I'm a person with a very little knowledge of database administration and query construction. I searched on Google and read few questions at Stackoverflow about 'creating a new table for each user or using one table with a user_id column to separate each user's data'.
It seems that the question I'm going to ask is more like 'Need directions' then 'approximation of MySQL query time'.
If I go with only one database-table design then the table will have structure like this:-
+----------------------+---------+-------+-------+-------+-------+
| col_with_primary_key | user_id | col_1 | col_2 | col_3 | col_4 |
+----------------------+---------+-------+-------+-------+-------+
| 1 | A | aa | bb | cc | ll |
| 2 | A | dd | cc | ff | dd |
| 3 | B | zz | dd | yy | xx |
| 4 | C | ii | yy | kk | tt |
+----------------------+---------+-------+-------+-------+-------+
The user_id is differentiating which row is of which user.
Approximately, how much time will it take for MySQL to run the following query, given 10 Million records in the table.
SELECT *
FROM one_table
WHERE user_id = 'A'
Best Answer