What are indexes in a database context?

Question

I read on the Internet for a while when using indexes really speeds up your database queries.

My question is what are indexes? Why do they speed up queries?

Answer 1

Simply put, it provides efficient data retrieval.

Taking the phone book, it is always a "last name, first name" so you can find someone. Imagine if the telephone company simply added new numbers to the end of the list without any orders: you would have to scan millions of records one by one to find Smith, John.

Well, the same goes for the database table. A table without an index is (simply) called a "heap": because your data is literally a bunch of unordered data. If I have a million lines, I have to look at each line to find what I want.

Of course, this is more complicated than that, but I hope this reflects the essence.

The same applies everywhere: street names in the AZ manual are always in alphabetical order, the entries in your bank statement are always listed in date order

Answer 2

An index is a copy (part) of a table that becomes smaller for storage in memory than the whole table, thereby speeding up read operations.

Answer 3

Indexes are a tricky thing, some markers are:

• Indexes, like in a book, create a pointer to data.
• The index needs maintenance; they become fragmented over time.
• service data for CREATE and UPDATE as the index should be updated when data changes
• Primarily used to speed data extraction

There are various types of indexes and are very specific to each implementation. Basically, there is no exact science for creating indexes, and they are crucial for the application.

IMO, one of the best articles for the beginner index, actually a collection of articles. This requires an account, but a free and large SQL information resource.

Answer 4

Have you ever searched the contents of a book using your index? Usually you see an index page to see the chapter you need on which page, and then go directly to that page, rather than search all pages.

This is very similar to how the indexes work in the table - depending on which column (s) the query filter is included in, the index on this column (s) is checked, which gives the location of the corresponding rows in real physical memory. This is much faster than searching all the rows individually. In addition, indexes are usually ordered (whereas actual rows may not be), which allows you to use the best search algorithms, for example, binary search scanning.