Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Index all columns

Knowing that an indexed column leads to a better performance, is it worthy to indexes all columns in all tables of the database? What are the advantages/disadvantages of such approach?

If it is worthy, is there a way to auto create indexes in SQL Server? My application dynamically adds tables and columns (depending on the user configuration) and I would like to have them auto indexed.

like image 665
Moslem Ben Dhaou Avatar asked Oct 16 '12 13:10

Moslem Ben Dhaou


People also ask

Can I index all columns?

No, you should not index all of your columns, and there's several reasons for this: There is a cost to maintain each index during an insert, update or delete statement, that will cause each of those transactions to take longer. It will increase the storage required since each index takes up space on disk.

What will happen if we place index on all columns?

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.

Can you index multiple columns?

A composite index is an index on multiple columns. MySQL allows you to create a composite index that consists of up to 16 columns. A composite index is also known as a multiple-column index.

Should I index all columns MySQL?

No, you should not index all columns. It will be slower when you write data.


2 Answers

It is difficult to imagine real-world scenarios where indexing every column would be useful, for the reasons mentioned above. The type of scenario would require a bunch of different queries, all accessing exactly one column of the table. Each query could be accessing a different column.

The other answers don't address the issues during the select side of the query. Obviously, maintaining indexes is an issue, but if you are creating the table/s once and then reading many, many times, the overhead of updates/inserts/deletes is not a consideration.

An index contains the original data along with points to records/pages where the data resides. The structure of an index makes it fast to do things like: find a single value, retrieve values in order, count the number of distinct values, and find the minimum and maximum values.

An index does not only take space up on disk. More importantly, it occupies memory. And, memory contention is often the factor that determines query performance. In general, building an index on every column will occupy more space than then original data. (One exception would be a column that is relative wide and has relatively few values.)

In addition, to satisfy many queries you may need one or more indexes plus the original data. Your page cache gets rather filled with data, which can increase the number of cache misses, which in turn incurs more overhead.

I wonder if your question is really a sign that you have not modelled your data structures adequately. There are few cases where you want users to build ad hoc permanent tables. More typically, their data would be stored in a pre-defined format, which you can optimize for the access requirements.

like image 179
Gordon Linoff Avatar answered Oct 03 '22 03:10

Gordon Linoff


No because you have to take in consideration that every time you add or update a record, you have to recalculate your indexes and having indexes on all columns would take a lot of time and lead to bad performance.

So databases like data warehouses where there use only select queries is a good idea but on normal database it's a bad idea.

Also, it's not because you are using a column in a where clause that you have to add an index on it. Try to find a column where the record will be almost all unique like a primary key and that you don't edit often. A bad idea would be to index the sex of a person cause there are only 2 possible values and the result of the index would only split the data then it will search in almost every records.

like image 21
Marc Avatar answered Oct 03 '22 03:10

Marc