how do indices in mysql tables (MyISAM) work?

Question

Few basic doubts I have:

1. Is primary key column automatically indexed?

2. What should be criteria to select index column?

3. When should I club multiple columns?

4. Does MyISAM or InnoDB has any affect on which columns should be indexed? Probably not.

5. Are they really required, specially in case if primary key column is automatically indexed?

Thanks.

Answer 1

1. Yes. Key is the synonym for index.
2. Database design and used queries. There is no sole answer.
3. When these multiple columns got used in the query. There is no sole answer.
4. Yes, engine doesn't affect indexing.
5. Usually it does. Any live applicadion does fech it's data based not only on the primary key. There is no sole answer.

The indexing is quite complex job. First, it should be done based on the real needs. If some of your query turns to run slow, it's time to add some indexes. If your query runs fast, no index needed. Next, run your query with word EXPLAIN in front of it, e.g. EXPLAIN SELECT * FROM table and see what it says. Usually it helps to determine, where to place an idnex.

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html - more explanations here