I was a bit annoyed by the lack of benchmarks for this online, so I ran a test myself. Note though that I don’t do it on a regular basic, so please check my setup and steps for any factors that could have influenced the results unintentionally, and post your concerns in comments. The setup … Read more
I would use a composite (multi-column) key. CREATE TABLE INFO ( t1ID INT, t2ID INT, PRIMARY KEY (t1ID, t2ID) ) This way you can have t1ID and t2ID as foreign keys pointing to their respective tables as well.
The main differences between InnoDB and MyISAM (“with respect to designing a table or database” you asked about) are support for “referential integrity” and “transactions”. We choose InnoDB if we need the database to enforce foreign key constraints or support transactions (i.e. changes made by two or more DML operations handled as single unit of … Read more
SHOW TABLE STATUS WHERE Name=”xxx” This will give you (among other things) an Engine column, which is what you want.
I have briefly discussed this question in a table so you can conclude whether to go with InnoDB or MyISAM. Here is a small overview of which db storage engine you should use in which situation: MyISAM InnoDB —————————————————————- Required full-text search Yes 5.6.4 —————————————————————- Require transactions Yes —————————————————————- Frequent select queries Yes —————————————————————- Frequent … Read more