Don't worry! I know what all of the above are! I have a technical question (or two).
I am going through a client's database and optimizing the tables as best as I can. Some junction tables exist to link certain tables together. Let's say I have a junction table with two columns (some_id, another_id [and in that order]) that pairs primary keys of two other tables and so are, in effect, foreign keys. This junction table is obviously used in joins so both columns are being used whenever this junction table is referenced. The rows should be unique, so there is a composite unique index on both columns (which can be removed if necessary of course). I am aware that MySQL can now use this index if a WHERE clause references both columns or just the first (some_id) column. Is that enough to be optimized for tables that store large amounts of data? I know how to optimize statements and tables for WHERE clauses, but JOINs are another story. I read the docs, searched here, and searched on Google, and could not find any resources that solved my specific problem(s).
Problem 1: Creating a foreign key also creates an index on a column if it doesn't already exist right? So after explicitly defining each column as a foreign key, I'd have a composite index on both columns, and single indexes on each individual column? That sounds like overkill.
Problem 2: I want to optimize for JOINs as I mentioned earlier. If I am wrong about the behavior described in problem 1, then do I need to create an index on the second column? That way the composite index handles queries referencing (some_id) and (some_id, another_id) and then I'll have the single index just on the second column.
Assume that there is a fair amount of data retrieval going on and that my queries are optimized to reference (some_id) before (another_id). Also, redesigning the database structure is not an option (for this project).