How many rows are 'too many' for a MySQL table? [duplicate]

This question already has开发者_StackOverflow answers here: Closed 11 years ago.

Possible Duplicate:

How many rows in a database are TOO MANY?

I am building the database scheme for an application that will have users, and each user will have many rows in relation tables such as 'favorites'. Each user could have thousands of favorites, and there could be thousands of registered users (over time).

Given that users are never deleted, because that would either leave other entities orphaned, or have them deleted too (which isn't desired), and therefore these tables will keep growing forever, I was wondering if the resulting tables could be too big (eg: 1kk rows), and I should worry about this and do something like mark old and inactive users as deleted and remove the relations that only affect them (such as the favorites and other preferences).

Is this the way to go? Or can mysql easily handle 1kk rows in a table? Is there a known limit? Or is it fully hardware-dependant?

---

I agree with klennepette and Brian - with a couple of caveats.

If your data is inherently relational, and subject to queries that work well with SQL, you should be able to scale to hundreds of millions of records without exotic hardware requirements.

You will need to invest in indexing, query tuning, and making the occasional sacrifice to the relational model in the interests of speed. You should at least nod to performance when designing tables – preferring integers to strings for keys, for instance.

If, however, you have document-centric requirements, need free text search, or have lots of hierarchical relationships, you may need to look again.

If you need ACID transactions, you may run into scalability issues earlier than if you don't care about transactions (though this is still unlikely to affect you in practice); if you have long-running or complex transactions, your scalability decreases quite rapidly.

I'd recommend building the project from the ground up with scalability requirements in mind. What I've done in the past is set up a test environment populated with millions of records (I used DBMonster, but not sure if that's still around), and regularly test work-in-progress code against this database using load testing tools like Jmeter.

---

Millions of rows is fine, tens of millions of rows is fine - provided you've got an even remotely decent server, i.e. a few Gbs of RAM, plenty disk space. You will need to learn about indexes for fast retrieval, but in terms of MySQL being able to handle it, no problem.

---

Here's an example that demonstrates what can be achived using a well designed/normalised innodb schema which takes advantage of innodb's clustered primary key indexes (not available with myisam). The example is based on a forum with threads and has 500 million rows and query runtimes of 0.02 seconds while under load.

MySQL and NoSQL: Help me to choose the right one

---

It is mostly hardware dependant, but having that said MySQL scales pretty well. I wouldn't worry too much about table size, if it does become an issue later on you can always use partitioning to ease the stress.