What happens if I put an index in each column of the table

Question

Let's consider that I have a table with 60 columns, I need to execute all kinds of queries in this table, and I need to also join this table with other tables. And I almost use all the rows to find the data in this table, including other tables. This table is similar to the main table (for example, the primary key) in the database. Thus, the entire table refers to this table. After reviewing the above scenario, I can create an index for each column of the table (60 columns), is this good practice?

In one 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? 

If the index can be “Primary Key,” “Unique Key,” or “Index”

Please comment if this question is not clear to you, I will try to improve this issue.

Answer 1

The MySQL documentation is pretty straightforward for this (in the generalized column usage metrics that you'll use in WHERE , JOIN and aggregation functions).

Therefore, there is nothing wrong with creating an index for all columns in a table, even if it is 60 columns. The more indexes, the slower inserts and some updates will be due to the fact that MySQL should create keys, but if you do not create indexes, MySQL should scan the entire table if only non-indexed columns are used in comparisons and joins.

I have to say that I am surprised that you

• A table with 60 columns
• Have all of these columns used in either the JOIN or WHERE regardless of any other column in the same table.

... but this is a separate issue.

Answer 2

Adding an index means that the database must support it , it means that it must be updated, so the more records you have, the more the index will be updated.

Creating an index out of the box is not a good idea, create an index only when you need it (or when you can see the need for the future ... only if it is pretty obvious)

Answer 3

It is not recommended that you create an index for each column in the table.

Indexes are most often used to improve query performance when a 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 have an index on them by default, so when you join a table with other tables, you must use the primary key to join.