What are the biggest benefits of using INDEXES in

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

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.

Answer 2

Think of it this way: what are the biggest benefits of having an index in a book? It's much the same thing. You have a slightly larger book, yet you're able to quickly look things up. When you create an index on a column, you're saying you want to be able to reference it in a where clause to look it up quickly.

Answer 3

You don't have to have a primary key. Indexes (of any type) are used to speed up queries and, at least with the InnoDB engine, enforce foreign key constraints. Whether you use a unique or plain (non-unique) index depends on whether you want to allow duplicate values in the key.

Answer 4

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 5

An index allows MySQL to find data quicker. You use them on columns that you'll be using in WHERE clauses. For example, if you have a column named score, and want to find everything with where score > 5, by default this means MySQL will need to scan through the WHOLE table to find those scores. However if you use a BTREE index, finding those that meet that condition will happen a LOT faster.

Indices have a price: disk and memory space. If it's a very big table, your index will grow rather large.

Answer 6

The easiest way to think about an index is to think about a dictionary. It has words and it has definitions corresponding to those words. The dictionary has an index on "word" because when you go to a dictionary you want to look up a word quickly, then get its definition. A dictionary usually contains just one index - an index by word.

A database is analogous. When you have a bunch of data in the database, you will have certain ways that you want to get it out. Let's say you have a User table and you often look up a user by the FirstName column. Since this is an operation that you are doing often in your application, you should consider using an index on this column. That will create a structure in the database that is sorted, if you will, by that column, so that looking up something by first name is like looking up a word in a dictionary. If you didn't have this index you might need to look at ALL rows before you determine which ones have a specific FirstName. By adding an index, you have made this fast.

So why not put an index on all columns and make them all fast? Like everything, there is a trade off. Every time you insert a row into the table User, the database will need to perform its magic and sort everything on your indexed column. This can be expensive.