I’m boning up on architecture design while I get ready for a big community site we’re making. I learned some new things about MySQL this morning (reading Ben’s “High Performance MySQL” book I thought would be useful to share:

• MySQL will only ever use one index per table per query. Therefore if you have a query that has multiple columns it’s matching in it’s where statement, it only matters that one of them is indexed. And, if I’m correct, you’re better off having the column with the most diversity indexed.
• This sort of follows from the previous, but this where multi column indexes come in handy. To use the book’s example, if you are always SELECTing based on last name and first name, if they are each separate indexes only one will be used. Thus, to fully optimize that SELECT, you’d want to have a multicolumn index over both of them
• If you’re using a UNIQUE column just as a way to not allow duplicate entries in a table, it adds overhead. You may be better of checking for duplicates in your application before INSERTing. This may be an occasion to use InnoDB’s transactions too.