Does the number of rows in mysql table matter?

Question

I'm coming up with a web app which will want me to store more than 80 Lakh (8 million) rows. Will it be fine to handle those many number of rows with MySQL without having any performance degradation? Assume my RAM to be 4 GB and Infinity GB Harddisk space. Also, the main fields have been indexed.

Answer 1

I'm not sure what 80 Lakh is? But as long as your indexes can fit in memory, performance should not take a massive hit. With 4gb of ram I would guess you could do 20m - 30m rows?

Answer 2

I believe performance is relative to alot of different things. For instance what is the row size? what kinda of data are you putting in there? what sort of operations do you want to perform on these records? For instance text matching would surely degrade performance with more records in there.

Answer 3

You will have to try to find out for sure, but I think 8 million rows shouldn't be much of a challenge for a modern, well-equipped server, at least not for reading operations in a well-indexed table.

Operations like ALTER TABLE and others that can't make use of the indexes are bound to take longer than in an empty table - how much longer, you'll have to find out.

The question you should always ask yourself is whether you really need all those 8 million rows on a daily basis. If you're generating logs, it makes sense to frequently archive older entries and to remove them from the live database.

You are mentioning Stack Overflow as a comparison - look at their server setup. It's larger than you mention of course, but not that much - and they have to manage tens of thousands of requests every minute.

Answer 4

I have a table which is very often read (written once every day at 4:00 AM), and it recently reached 8 million rows after a huge import.

The query also joins this table to another ~400k rows table.

There was no performance degradation after the import, compared to before it, so I believe you should be just fine, with a good server. (We have a dual-quad core, 8GB RAM)