What are the effects of indexing on database tables?

The working of Index in database tables is the same as index work in a Text Book. It will help to browse the desired content faster in a book. we use to see the index on the first page then we check the page where it is in the book and then we directly come to that page, instead of going through all pages.

Now databases, same happen with tables, if you are applying select query on the table, it will also check for the index and then return you the required field. It will return the field faster on which index is applied.
Or in language,

• we can say that the index one query will execute faster instead of the non-indexed one.
• Join will execute faster on indexed one.

eg- Consider a Table Product with `id` as Primary Key

•  id
• name
• price
• category_id 
• location_id           

    Table Info-

Now  Applying Index on category_id 

SELECT category_id FROM product WHERE category_id='5';

SELECT category_id FROM product WHERE location_id='10';

First one will excute faster than the second one.