Primary Key and Indexes

This lesson explains the concept of an index and its utility in databases.

Primary Key and Indexes

Every time a query that contains a WHERE clause is issued, MySQL has to do a full scan of the table to find matching rows. A linear scan of the table isn’t very efficient. Adding an index to the table can significantly speed-up search for matching rows. An index’s primary purpose is to provide an ordered representation of indexed data. An index works similar to how a book index works. Say if you are looking for a particular word, you can scan the index and find the page numbers where the word appears. This saves you time as now you don’t have to flip through every page in the book. Since a book index is sorted in alphabetical order, you can visually very quickly narrow down the portion of the index to inspect. If you are looking for a topic starting with the letter ‘M’, you can jump to the part of the index with all the topics starting with the letter ‘M’. However, the trade-off is that the index itself takes up a few pages of the book.

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/16lesson.sh and wait for the MySQL prompt to start-up.

Level up your interview prep. Join Educative to access 70+ hands-on prep courses.