What is the benefit of indexing in a database system? Indexing is a very easy way to improve the performance of your database. It’s easy because the concept is not hard to grasp and it takes little time to complete. An index is a structure that is usually independent of the data in the table. It’s solely for executing queries more efficiently.
There are many common indexes we have all seen before; in the back or front of a textbook, a telephone book, the entire dictionary is basically an index start to finish. Going off what we were talking about in class, an index can make finding someone out of a 1000 people only about 4x as easy as finding someone out of a billion people. That amazes me because of how different the scale is. You would think it would take days to find a single person out of a billion. Learning the concept for why we typically use an index is easy because of the practicality of it and how often we see it.
Without an index, a database much scan every row (with millions of rows, simple queries typically take 3-5 seconds. That sounds quick but remember these are simple select queries only). With an index, a database only has to search for the records that match a certain condition (with millions of rows, simple queries take .5 to 1 second to complete). This improvement to efficiency only applies to columns that are already indexed.
The database chooses the order in which it searches for data. It doesn’t matter what order you run the query, as long as your indexed column is part of it. The database has something known as a query optimizer; it chooses the best query plan and runs the query. Databases can have more than one index.
How to create a simple index:
CREATE INDEX TableName_ColumnName_idx ON TableName(ColumnName);
(Building an index by scratch with millions of rows will take a few minutes)
How to create a multi-column index:
CREATE INDEX TableName_C1_C2_idx ON TableName(C1,C2);
(order matters, in this case C1 will be searched for first.)
Conclusion: Indexes really speed up the process of running queries when done correctly. However, indexes are not free. Think of the extra pages used up in a textbook.
Https ://youtu.be/fsG1XaZEa78 (I put a space in between the s and : so you could copy the link and view the video because last time the link got blocked).