0

For what practical purposes would I'd potentially need to add an index to columns in my table? What are they typically needed for?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Billy Logan
  • 2,470
  • 6
  • 27
  • 45
  • 1
    http://use-the-index-luke.com –  Jan 02 '16 at 19:58
  • Possible duplicate of [How does database indexing work?](http://stackoverflow.com/questions/1108/how-does-database-indexing-work) – Paul92 Jan 03 '16 at 00:58

3 Answers3

1

Indexes are database structures that improve the speed of retrieving data from the columns they are applied on. The wikipedia article on the subject gives a pretty good overview without going in to too much implementation-specific details.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
1

Basic indexes have two common uses.

  • They speed up queries.
  • They implement unique constraints (and hence help define primary keys).

In addition, specialized indexes can enable functionality in some databases, in particular, text search and GIS queries.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Indexing columns speeds up queries on tables with many rows.

Indexes allow your database to search for the desired row using searching algorithms like binary search.

This would only be helpful if you had a large number of rows, for example 16 or more (this number is taken from the quicksort algorithm, which says if sorting 16 or less items, just do an insertion sort). Otherwise there would be negligible performance gain compared to a plain linear search.

If a table had 100 rows and you wanted to find the 80th row, without indexes, it might take 80 operations to find the 80th row. However with indexes, assuming they enable something like binary search, you could find the 80th row in something like 10 or less operations.

Magical Gordon
  • 404
  • 2
  • 8
  • Excellent comprehensive answer! – Billy Logan Jan 02 '16 at 20:09
  • 2
    I think you mean "rows" – Neil McGuigan Jan 02 '16 at 20:17
  • @NeilMcGuigan A quick google search shows that you can in fact index for columns as well. However, just my opinion, if a table has enough columns to merit indexing, perhaps a better solution would be to break those columns into separate tables. – Magical Gordon Jan 02 '16 at 20:21
  • 2
    @BillyLogan: SQL statements don't "search for columns" they "search for rows". And indexes _only_ support looking up rows in a table, not "searching" for columns. As it stands this answer is simply wrong (despite being upvoted and accepted) –  Jan 02 '16 at 21:12
  • @a_horse_with_no_name I tried to correct my answer. Billy's question was about column indexing and I misinterpreted that. – Magical Gordon Jan 02 '16 at 21:17
  • Well an index is defined for one or more _columns_ - but it still is used to lookup _rows_ (that contain a specific value in of of the indexed columns) –  Jan 02 '16 at 21:30