Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

Question

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'

Answer 1

• 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.