What are the biggest benefits of using INDEXES in mysql?

Question

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?

Answer 1

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).

Link:
MySQL indexes - what are the best practices?

Answer 2

Pros:

Faster lookup for results. This is all about reducing the # of Disk IO's. Instead of scanning the entire table for the results, you can reduce the number of disk IO's(page fetches) by using index structures such as B-Trees or Hash Indexes to get to your data faster.

Cons:

• Slower writes(potentially). Not only do you have to write your data to your tables, but you also have to write to your indexes. This may cause the system to restructure the index structure(Hash Index, B-Tree etc), which can be very computationally expensive.

• Takes up more disk space, naturally. You are storing more data.