mysql中index的作用,在mysql中使用INDEXES有什么好处？

I know I need to have a primary key set, and to set anything that should be unique as a unique key, but what is an INDEX and how do I use them?

What are the benefits? Pros & Cons? I notice I can either use them or not, when should I?

解决方案

Short answer:

Indexes speed up SELECT's and slow down INSERT's.

Usually it's better to have indexes, because they speed up select more than they slow down insert.

On an UPDATE the index can speed things way up if an indexed field is used in the WHERE clause and slow things down if you update one of the indexed fields.

How do you know when to use an index

Add EXPLAIN in front of your SELECT statement.

Like so:

EXPLAIN SELECT * FROM table1

WHERE unindexfield1 > unindexedfield2

ORDER BY unindexedfield3

Will show you how much work MySQL will have to do on each of the unindexed fields.

Using that info you can decide if it is worthwhile to add indexes or not.

Explain can also tell you if it is better to drop and index

EXPLAIN SELECT * FROM table1

WHERE indexedfield1 > indexedfield2

ORDER BY indexedfield3

If very little rows are selected, or MySQL decided to ignore the index (it does that from time to time) then you might as well drop the index, because it is slowing down your inserts but not speeding up your select's.

Then again it might also be that your select statement is not clever enough.

(Sorry for the complexity in the answer, I was trying to keep it simple, but failed).