How many rows in a database are TOO MANY?

I've a MySQL InnoDB table with 1,000,000 records. Is this too much? Or databases can handle this and more? I ask because I noticed that some queries (for example, getting the last row from a table) are slower (seconds) in the table with 1 millon rows than in one with 100.

Solution 1:

I've a MySQL InnoDB table with 1000000 registers. Is this too much?

No, 1,000,000 rows (AKA records) is not too much for a database.

I ask because I noticed that some queries (for example, getting the last register of a table) are slower (seconds) in the table with 1 million registers than in one with 100.

There's a lot to account for in that statement. The usual suspects are:

1. Poorly written query
2. Not using a primary key, assuming one even exists on the table
3. Poorly designed data model (table structure)
4. Lack of indexes

Solution 2:

I have a database with more than 97,000,000 records(30GB datafile), and having no problem .

Just remember to define and improve your table index.

So its obvious that 1,000,000 is not MANY ! (But if you don't index; yes, it is MANY )

Solution 3:

Use 'explain' to examine your query and see if there is anything wrong with the query plan.

Solution 4:

I think this is a common misconception - size is only one part of the equation when it comes to database scalability. There are other issues that are hard (or harder):

• How large is the working set (i.e. how much data needs to be loaded in memory and actively worked on). If you just insert data and then do nothing with it, it's actually an easy problem to solve.

• What level of concurrency is required? Is there just one user inserting/reading, or do we have many thousands of clients operating at once?

• What levels of promise/durability and consistency of performance are required? Do we have to make sure that we can honor each commit. Is it okay if the average transaction is fast, or do we want to make sure that all transactions are reliably fast (six sigma quality control like - http://www.mysqlperformanceblog.com/2010/06/07/performance-optimization-and-six-sigma/).

• Do you need to do any operational issues, such as ALTER the table schema? In InnoDB this is possible, but incredibly slow since it often has to create a temporary table in foreground (blocking all connections).

So I'm going to state the two limiting issues are going to be:

• Your own skill at writing queries / having good indexes.
• How much pain you can tolerate waiting on ALTER TABLE statements.