0

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'
1
  • after giving a good information about the db design you are trying to achieve the question you asked is not appropriate, because it is clearly visible that the answer depends on how many rows you will have in that DB for that User. if there is only One row and you have appropriate index it will be quick but as the number of rows increases the time will increase linearly or exponential depending on the table/index/query structure. note that when you are looking for execution time, the query may finish in 2 min but the time taken by APP to render this data to user is Add on to execution time.
    – Anup Shah
    Feb 27, 2014 at 20:38

1 Answer 1

2
  • 10 million records is nothing to MySQL.
  • Database design is VERY importmant - INDEX the field(s) that will be searched.
  • Don't index every field.
  • When you get into the billions of records then look into Partitioning tables.
  • RAM, more RAM, and just to be sure add more RAM. MySQL loves RAM.
  • For best speeds, make sure the MySQL server is a dedicated box - NOT a VM.
  • I would lean more towards Linux for the OS.
  • Hire a database admin.
1
  • Thank you. That answer was very satisfying. I'll surely look into indexing field(s).
    – Omar Tariq
    Mar 2, 2014 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.