What happens if I put index on each column in a table

Question

Let us consider I have a table with 60 columns , I need to perform all kind of queries on that table and need to join that table with other tables as well. And I almost using all rows for searching data in that table including other tables. This table is the like a primary table(like a primary key) in the database. So all table are in relation with this table. By considering the above scenario can I create index on each column on the table (60 columns ) ,is it good practice ?

In single sentence:

Is it best practice to create index on each column in a table ?
What might happens if I create index on each column in a table?

Where index might be "Primary key", "unique key" or "index"

Please comment, if this question is unclear for you people I will try to improve this question.

Answer 1

MySQL's documentation is pretty clear on this (in summary use indices on columns you will use in WHERE, JOIN, and aggregation functions).

Therefore there is nothing inherently wrong with creating an index on all columns in a table, even if it is 60 columns. The more indices there are the slower inserts and some updates will be because MySQL has to create the keys, but if you don't create the indices MySQL has to scan the entire table if only non-indexed columns are used in comparisons and joins.

I have to say that I'm astonished that you would

1. Have a table with 60 columns
2. Have all of those columns used either in a JOIN or WHERE clause without dependency on any other column in the same table

...but that's a separate issue.

Answer 2

It is not best practice to create index on each column in a table.

Indexes are most commonly used to improve query performance when the column is used in a where clause.

Suppose you use this query a lot:

select * from tablewith60cols where col10 = 'xx';

then it would be useful to have an index on col10.

Note that primary keys by default have an index on them, so when you join the table with other tables you should use the primary key to join.