With most database systems, mysql included, every table lookup can be qualified with a database (aka schema in other systems) name.
SELECT * FROM Members.access WHERE ...
As long as you have access to each of the schema you're accessing you can do it from a single database handle. It's essentially a way of fully qualifying the table name.
If it makes sense to split your tables across several schema then do so; perhaps there's some logical grouping that makes sense. However, if you're just doing it because 60 seems like an awful lot of tables I'd recommend against it; your groupings will probably turn out a little arbitrary, and database systems don't typically have a problem with that many tables in one schema.
| [reply]
[d/l] |
Connections to mysql are fairly cheap on the client side, but can eat up a good chunk of memory server side (depending on lots of things that a good DBA could explain better than I can).
If the databases are on the same physical machine it'd probably be more efficient to combine them into one database.
Of course as things grow you may hit a point where you have to split them up or it becomes more efficient to do so.
| [reply] |
| [reply] |
Actually the op was asking about efficiency. If the databases are on the same machine using mysql as he stated, then it is more efficient to have one connection to one database.
If the data grows to the point that you can't keep it all on one machine then you can be more efficient by partitioning it into different databases on different machines.
| [reply] |
However, a there may be a downside (if it is considered one), which is having 60 tables inside one single database.
Man, don't sweat the small stuff. Even if MySQL is considered a toy by some people, 60 tables is barely starting to get warm. I wouldn't even begin to worry until there were a couple of thousand, if they're all part of the same application. (Well I would for reasons like replication and hot backups and other such issues, but I suspect that this is not pertinent to the discussion).
It seems to me that it must be quite possible to write a query that returns a result set containing information combined from member info, posting comments and blog posts. In which case it makes complete sense to have them all in a single database. Cross-schema queries should be avoided if you have the choice.
• another intruder with the mooring in the heart of the Perl
| [reply] |
Speaking as a programmer and MySQL DBA, additional connections are a nothing relative to everything else. Period, end of story, don't even worry about it.
Instead, you should be worrying about the following:
- Are your tables properly normalized?
- Are you using all the features provided by your RDBMS to ensure data integrity?
- Are your primary keys database-generated, meaning that your user-facing information can be easily altered? The perfect example of this is using an auto-generated id vs. the username as the primary key for your users table.
Remember - the point of a database has more to do with keeping your precious data secure from the stupid programmers who want to pollute it and less to do with the speed with which you can get answers to questions. If your data is corrupted, then you're going to get very fast answers that are wrong. Start with correctness and add speed later.
My criteria for good software:
- Does it work?
- Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
| [reply] |