From your experience, what's the upper limit of rows in a MyISAM table can MySQL handle efficiently on a server with Q9650 CPU (4-core, 3.0G) and 8G RAM.
I currently have a table with 15 million rows. It's pretty fast. If the scale increases to 1 billion rows, do I need to partition it into 10 tables with 100 million rows each?
Best Answer
I would not worry about application performance with 1 billion rows on a machine that can keep the indexes in memory. If you are serious about reaching 1 billion rows, you first have to do some math:
Next, move into your application uptime requirements.
I would worry more about the data lifecycle and data management of a multi-gigabyte table file of that magnitude before worrying about performance. With replication, you can make up a lot of the performance. Keeping the data sane and restoring from even small disasters (like corruption induced from bad ram) is more likely going to trouble you first.
I would also encourage you to take the table you have -- and add 1B rows of test data to it. This is extremely insightful to watch what happens to your system. Run some EXPLAINs on your queries against this new huge dataset. Time how long it takes to backup, and restore. You might need to adjust some requirements.
This is an interesting article about 1 billion rows in mysql.