What is a database index?

Question

I heard them talking, since I started working in technology about 18 months ago. I know that they potentially improve performance, and they seem to refer to columns - ("We index the User table in the date_of_birth column").

Just find a brief overview of exactly what they represent, what they are used for and how they work.

Answer 1

I wrote a complete book about it! It is also available free of charge on the Internet: http://use-the-index-luke.com/

I will try to answer your questions in the near future - this is not quite what I am capable of. The last time I tried, I finished writing a book ...

Like tables, indexes consist of rows and columns, but store data in a logically sorted way to improve search efficiency. Think of it as a phone book (printed). They are usually sorted by last_name , first_name and potentially other criteria (e.g. zip code). This sorting allows you to quickly find all the records for a specific last name. If you know the first name, you can even quickly find entries for the surname / first name combination.

If you only know the name, the phone book really will not help you. The same is true for multi-column database indexes. So yes, an index can potentially improve search performance. If you have the wrong index for your question (for example, a phone book when searching by name), they may not be useful.

You can have many indexes in the same table, but in different columns. Thus, the index on last_name , first_name differs from the index only on first_name (which you will need to optimize the search by name).

Indexes contain redundant data (for example: clustered indexes = phone book). They have the same information as in the table (for example: functional indexes ), but are sorted. This redundancy is automatically maintained by the database for each write operation you perform ( insert / update / delete ). Consequently, indexed performance is reduced .

In addition, to quickly find data, indexes can also be used to optimize order by operations and physically sort related data together ( clustering ).

To get a better idea, view the full contents of my book: http://use-the-index-luke.com/sql/table-of-contents

Answer 2

Think of it as a table of contents for tables. If he is there, the database knows where to look more specifically. If not, the database must look for all the data in order to find it.

A more detailed explanation can be found here in this Wikipedia article .

Answer 3

A database index is a data structure aimed at improving the time complexity of a search operation.

Searching without an index in the worst case is O(N) complexity. Efficient index search allows logarithmically O(log(N)) or even with some complexity of O(1) architecture.

The database index also allows you to apply database restrictions. Many database systems set an index in a column set called a PRIMARY KEY . Some database systems require indexing columns in FOREIGN KEY to speed up operations (insert, update).

Answer 4

An index is an optional structure associated with a table or table cluster, which can sometimes speed up access to data. By creating an index in one or several columns of a table, you get the opportunity in some cases to extract a small set of randomly distributed rows from a table. Indexes are one of many ways to reduce disk I / O.

If there are no indexes in the heap-organized table, then the database must perform a full table scan to find the value. For example, without an index, a 2700 location query in the hr.departments table requires the database to search each row in each block of the table for this value. This approach does not scale very well as data volumes increase.

http://docs.oracle.com/cd/E11882_01/server.112/e10713/indexiot.htm

Answer 5

It has a very similar stream here . Check it out is helpful.

I know that they potentially improve performance

Yes it's true. But please keep this in mind, sometimes indexing can be the cause of Performance . Example: indexing all columns of a database will undoubtedly affect performance.