MySQL: What is an index needed for?

I would like to explain the index with a simple example:

Example

Imagine you have a phonebook and you want to find a number for a specific name. This task is relatively simple. You open the phone book and know immediately if your name is in front of or after the name you are looking at. So you can find the number quickly and efficiently.

But now imagine another phone book that is not sorted by name and in which all names are confused. Again you should find a number and look for the name. But this time the search is anything but efficient. If you're not opening the phone book by accident and find the right number this way by incident, you'll need to go through the whole phone book from start to end to see if your name is anywhere. This search is anything but efficient and takes a lot of time.

It's the same with databases and MySQL. In the first example with the phone book, an index is placed over the "Name" column and the search can be done quickly, in the second example there is no such index and the search is slow and tedious.

Index in MySQL

So, if you have a table that has a column with a unique number (ID) and you want to keep reading data from a row with a specific ID, you should index that column. Only if you have an index on this column, MySQL can efficiently search for a particular number as in the phone book, otherwise all records must be gone through.

Of course, if MySQL is unable to search efficiently for a few records, it's hardly noticeable because of course computers are searching fast. But even that consumes unnecessary resources and as soon as many records are available and / or many users access the database at the same time, a well-placed index pays off immediately!

Index over each Column?

One might now think, "Great, I just put an index over each column and everything works fine!". But it's not that easy, an index also has its disadvantages.

First, an index consumes extra storage space and, most importantly, an index needs to be maintained. Each time you insert new data and whenever you change any data related to the index, the index must be adjusted and updated.

So, if you've created 30 indices over columns that are never searched for, but are constantly being changed, the perceived benefit is a disadvantage. So you should think carefully about which columns can really profit by an index and index only those columns.
2018-03-24 at 20:20