So reading this old blog post (which is quite old to be fair), gave me some thoughts on how to structure some of my table relationships.
If any secondary index look ups contain the primary key and the primary key is what accesses the row data, then for this sort of schema,
Users : id, name, country, etc.
User_Mailboxes : id, user_id, location, height, etc.
Where a user may or may not have a mailbox but at max one, would it be more efficient to get rid of the 'id' as the primary key of the user_mailboxes table and set the foreign key as the primary key?
From my understanding of InnoDB, that way we'd save any secondary index look ups for the corresponding primary key and be able to use the User.id directly to find the related mailbox info.
So something more akin to this,
Users : id (PRIMARY), name, country, etc.
User_Mailboxes : user_id(FOREIGN, PRIMARY), location, height, etc.
Should be slightly more performant in terms of index storage and random lookups? Especially if I'm considering grabbing a bunch of mailboxes at once based on some user criteria?
In a 1:0..1 relationship you can (and should) do that, yes. I would do so, too. Storing unneeded information isn't good anyway.
But don't be disappointed, when you don't gain as much performance as you think. When you don't grab a really large amount of users/mailboxes at once, you won't notice much or anything at all.