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.

Press + to interact
-- The lesson queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
SHOW INDEX FROM Actors;
-- Query 2
ANALYZE TABLE Actors;
SHOW INDEX FROM Actors;
-- Query 3
INSERT INTO Actors (Id, FirstName, SecondName,DoB, Gender, MaritalStatus, NetWorthInMillions) VALUES (15, "First","Row", "1999-01-01", "Male", "Single",0.00);
INSERT INTO Actors (Id, FirstName, SecondName,DoB, Gender, MaritalStatus, NetWorthInMillions) VALUES (13, "Second","Row", "1999-01-01", "Male", "Single",0.00);
INSERT INTO Actors (Id, FirstName, SecondName,DoB, Gender, MaritalStatus, NetWorthInMillions) VALUES (12, "Third","Row", "1999-01-01", "Male", "Single",0.00);
Terminal 1
Terminal
Loading...

We can use the following query to display the indexes on a table:

SHOW INDEX FROM Actors;

The cardinality shows the number of unique values for the primary key. It’s also described as an estimate of the number of unique values in the index and may not be exact for smaller tables. Since we have eleven actors in the Actors table we have cardinality as 11. However, if you execute the above query in the console, the cardinality may not come out to be 11. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. However, if you execute the following command and then check for ...

Get hands-on with 1400+ tech skills courses.