0 0 Votes

MySQL: What is an index needed for?

Question by SmartUser | 24/03/2018 at 13:24

I do not know much about databases and MySQL, but I am currently working on my first Internet projects. Now and again I come across the idea that it should be important to define an index over certain columns.

What is this index and why is an index necessary at all?

ReplyPositiveNegative

Stefan Trost

Show Profile | Message
Avatar
0Best Answer
0 Votes

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.
24/03/2018 at 20:20

ReplyPositive Negative
Reply

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.