Indexes in MySQL - 101 intro?

I have worked with creating tables and keys, basic stuff. No experienc开发者_如何学JAVAe with indexes. So the question is: Do I need to manually create any index or does MySQL auto create it for me for all tables i create?

If I need to create them does it mean any column with a PK and FK needs to be indexed or is this business logic related on which columns need indexes and which dont.

You need to create them yourself. PKs are actually indexes themselves, so there's no need to double index those columns. And FKs can only be implemented against indexed columns...

As far as when and why to implement indexes, I'd suggest reading some of the following:

1. The official documentation
2. MySQL Performance Blog
3. A sitepoint index

One thing to note, don't just go blindly adding indexes everywhere. That's generally a bad idea. Instead, plan and test. Only add indexes that give you a measurable gain in your particular usage. Indexes are not free, so don't throw them around lightly...

You have to create them.

PK are indexes, FK too. (so no need to create an index over a PK or a FK)

You must create indexes on any column that appear on a WHERE statement.