CODEWITHSUNDEEP
mysqldatabase-designrdbms
Jayp
Jayp

Reputation: 1

Best approach to relating databases or tables?

What I have:

The query:

or

Upvotes: 0

Views: 106

Answers (4)

Javier
Javier

Reputation: 62593

When you say 'same database' and 'different databases related' don't you mean 'same table' vs 'different tables'?

if that's the question, i'd say:

  • one table for articles
  • if these 'other sets of data' are all of the same structure, put them all in the same table. if not, one table per kind of data.
  • everything on the same database

if you grow big enough to make database size a performance issue (after many million records and lots of queries a second), consider table partitioning or maybe replacing the biggest table with a key/value store (couchDB, mongoDB, redis, tokyo cabinet, [etc][6]), which can be a little faster than MySQL but a lot easier to distribute for performance.

[6]:key-value store

Upvotes: 0

APC
APC

Reputation: 146239

Different databases on the same server gives you all the problems of a distributed architecture without any of the benefits of scaling out. One database per server is the way to go.

Upvotes: 0

Pentium10
Pentium10

Reputation: 207912

If you need to connect frequently and read both of the records, you should put in a the same database. The server then won't have to run permission checks twice for each of your databases.

If you have serious traffic, you should consider using persistent connection for that query.

If you don't need to read them together frequently, consider to put on different machine. As the high traffic for the bigger database won't cause slow downs on the other.

Upvotes: 0

pauljwilliams
pauljwilliams

Reputation: 19225

Put them all in the same DB initially, until you find that there is a performance issue. Much easier than prematurely optimising.

Modern RDBMS are very good at optimising data access.

Upvotes: 2

Related Questions