* Tofu Optimist > Three questions from a Mysql newcomer.... > > Syntax question: > > Can a table in database A have a foreign key > relationship with a table in database B? (both INNODB, > both with appropriate indexes, etc)
Yes, but I suspect that you really meant foreign key constraints? Constraints must be within the same database, AFAIK. <URL: http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html > If you really meant relationship, you can join tables from different databases using a db.table notation: SELECT db1.table1,field1, db2.table2.field2 FROM db1.table1, db2.table2 WHERE db1.table1.id = db2.table2.fk_id You can do this on any kind of table, not only InnoDB. > Architecture question: > > What is the rule-of-thumb to use use multiple > databases, rather than one huge database with many > tables? Is splitting one database into multiple > databases just a way to organize data (eg I use > folders and subfolders on my file system, not > everything is at root) or are there performance / > security / reliablity / backup / other issues? All of the above. :) The backup issue is important for MyIsam tables, because they are stored as separate files in the database folder. This means you can do backups using OS tools, like cp. InnoDb tables are stored in a few big files, you must backup all data at once or use special tools to backup individual databases. The performace issue relates to the filesystem, it's ability to handle many files in the same directory. The security issue: you can grant privileges on a database level, this is useless if all tables are in the same database. > System question: > > Mysql doesn't use transaction logs, does it? There is the binary log, storing everything for replication, including transactions: <URL: http://www.mysql.com/doc/en/Binary_log.html > > How does one restore a mysql database if it crashes in > the middle of a large operation? Not sure what you mean... I never take down the server, and it never crashes. :) Sometimes a table gets corrupt. Then I try to REPAIR it: <URL: http://www.mysql.com/doc/en/Repair.html > <URL: http://www.mysql.com/doc/en/REPAIR_TABLE.html > But this is only for MyIsam tables. I have no experience with InnoDB. If the REPAIR fails, ie too much data is lost, the most recent backup is the only rescue. The severity of this will depend on the table in question, a perifer table can be reconstructed or accepted as incomplete, a central table in a highly normalized structure can be fatal if it's lost, you may need to restore the entire database, not only the corrupt table. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
