mySQL: How many rows in a table before performance is affected?

Question

Hi everybody,

Is there a "known limit" for columns & rows in a mySQL table that when passed, it can be safe to say that performance is severely affected? I've think that I had heard that there is a "golden number" that you really dont want to exceed in either columns or rows in a table. - Or is it all about the size of the index and available RAM + CPU on the server?

Thanks!

Answer 1

MySQL has a hard limit of 4,096 columns, but it could be less because of the data types used. Personally, I really question the normalization when a table is pushing 30+ columns.

It depends on what you are doing - sometimes a monolithic table is faster. Given the low cost for CPU/RAM/drivespace, the limiting factor is the data model design first, followed by query construction IMO.

Answer 2

Aside from just accessing a table, you might want to consider the effects of maintenance. Reindexing or clustering a large table might take an unacceptably long amount of time, so you could break the table down to reduce the problems caused by locking the table or temporarily missing an index.

In general, the way to optimize is to try things out and measure the speed of typical queries.