Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

According to you, it's better to have a single DB with 1.000.000 rows or 5 DBs with 200.000 rows, in read/write?

share|improve this question

migrated from stackoverflow.com Feb 11 '11 at 15:34

This question came from our site for professional and enthusiast programmers.

5  
This question doesn't make sense... Why would you split a DB based on number of rows instead of how they relate to each other? Additionally 1.000.000 rows is not a lot. –  PatrikAkerstrand Feb 11 '11 at 8:31
1  
Your question is very dependent on the db structure, Are all the rows in a single table? What is the read/write ratio? What are the selectivity of the indexes? Are the rows being updated? Are the rows related? Have you looked at partitioning? –  The Scrum Meister Feb 11 '11 at 8:32
add comment

5 Answers 5

Well, if it were up to me, I'd take the table with 1,000,000 rows. Not necessarily for efficiency purposes, but because I'd lose my mind trying to keep track of data stored across five different tables which is actually the same set of data. There's too many ways to shoot yourself in the foot. Just for example, if I need to update a record with a certain key, which table am I going to look at?

And besides, when your usual approach fails because you have so many records in your table, you'll be forced to learn loads and loads about optimization, and so you will actually get a lot better at database management!

share|improve this answer
add comment

I would use rather one tabel whit 1 000 000 lines in single database.But you can google out what is Boyce-Codd normal form and I m sure you will get righter answer.

share|improve this answer
add comment

Unless you older records have fields that have be deprecated and it would be a nightmare to convert the older record then don't do it.

But you could stop inserting into the older databases, and start the id's at the next number in your newer database.

share|improve this answer
add comment

I assume by DB you mean Table. Anyway you miss an important piece of information: What storage engine are you using?

With MyISAM the split would make change since it uses Table Locks. With InnoDB and MVCC I assume it would make a big difference.

share|improve this answer
add comment

Don't you think that if splitting a table arbitrarily could somehow improves performance, then MySQL developers could have done so internally?

If there is any reason that splitting up the table can improve performance, then you just have an inefficient table design; try to normalize/denormalize the database such that such inefficiencies can be removed rather than trying to split the table arbitrarily.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.