6

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.

neotam
  • 2,611
  • 1
  • 31
  • 53
  • 3
    If you have an index on every column, the cost is additional storage space and a little bit of overhead on inserts and updates. – Scotch Apr 18 '13 at 05:49
  • Why it is overhead on insert and update operations – neotam Apr 18 '13 at 05:52
  • 3
    When you insert a new record into your table with 60 indexed columns, you also have to create the index entries for those 60 columns. (think of indexes as tiny tables, although they're typically trees). – Scotch Apr 18 '13 at 05:53
  • Possible duplicate of [MySQL - why not index every field?](http://stackoverflow.com/questions/5446124/mysql-why-not-index-every-field) – Ciro Santilli OurBigBook.com Nov 25 '15 at 11:30

4 Answers4

9

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.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • I am glad about your answer, well definitely I can say I am using more than half of columns in either join or where – neotam Apr 18 '13 at 05:59
3

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.

Jim
  • 221
  • 3
  • 8
2

Adding an index means that the database has to maintain it, that means that it has to be updated, so the more writes you have, the more the index will be updated.

Creating 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 in the future... only if it is pretty obvious)

Juan Antonio Gomez Moriano
  • 13,103
  • 10
  • 47
  • 65
1

creating more index in SQL will increase only search speed while you will get slowness of insert and update and also it will take more storage.

Mugeesh Husain
  • 394
  • 4
  • 13