So your index on a table works similarly as an index in a book.
For example you have a database book and you need to search for some information about storage. If there is no index, you would have to search every page one by one until you find it. But the index contains a list of keywords, so you will consult the index and see that storage is mentioned on particular pages so you can directly go to those pages and find out the topic.
if there is a book on databases and indexed the word "database", then you will find out that it's mentioned on pages 1-30,31-100, and 101 to 302. In these kind of scenarios, the index is not so helpful and it might be faster to go through the pages one by one.
But in case of a 10-page book, there is no sense to make an index, as you may end up with a 10-page book prefixed by a 5-page index, which is just silly - just scan the 10 pages and be done with it.
The indexes needs to be helpful because there's generally no point to index, for example the frequency of the letter "P" per page.
If you want to get more insights of SQL, checkout this SQL Course from Intellipaat.