• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 11/25/19

Dive into indexes

Log in or subscribe for free to enjoy all this course has to offer!

When we set up the Foreign Key Constraint in the dog table a few chapters ago, we also indexed the table's Foreign Key.

Now we'll go into more detail about why we did that and what an index looks like!

Select the  dog  table and then select the Structure tab.  You should see a screen that looks a bit like this:

The primary key is indexed by default.

But you can index more than just the primary key! The index helps the database find a record faster. 

A database index is just like the index in the back of a book. Which is to say that its a table with 2 columns: one for the values of the fields in the column that is getting indexed and one column that has the Primary Index for that row of the table.

Here are a few rows of the dog table, and below that is the index will look like for the the name column of the dog table.

dog table

dog_id

apt_id

name

breed

bites

...

...

...

...

...

77

44

Mongo

Poodle

1

78

3

Alby

mutt

0

79

7

Zonorp

Retriever

0

80

45

Betty

Shepard

1

...

...

...

...

...

Without  an index on the name column, if i ask the database to give me some information about the dog named Alby, the database would start at the beginning of the dog table with  dog_id  1 and would go through the table row by row to find Alby.  If you have a small database, this might not take long at all.  However, if the dog table here had hundreds of thousands of rows, this simple search could become slow.

Here's what the index of the dog table's name column will look like:

name

dog_id

Aaron

51

Alby

78

Borix

3

Brattle

6

The index of the name column stacks up the names in alphabetical order. If I ask the database to give me some information about the dog named Alby, it looks to the index of the  name  column, and find zeros in quickly on Alby's row because the index table is alphabetized. Then it finds Alby's  dog_id  and then it goes to the dog table to grab all the info I want from that row. 

However, you won't see this table - it will only exist in the background of the database for its internal use.

Let's create that table now!

In phpMyAdmin in the dog table's structure tab, click the word index on the row named name.  And you're done! To see which columns are indexed you have to click the word Indexes (the one that is circled in red).

The dog table's structure should now look like this:

The index list
The index list

A database administrator has to find the right compromise between reading speed and writing speed when they choose how many indexes to put on a table.

Often when setting up a database, you won't know what column will be used the most to search a specific table. The cool thing is that once you figure this out, you can just change what columns get indexed.

Altering the indexing of a table won't break your site. It will either make it run faster if you get it right - or slower if you get it wrong!

Try try again.

And that's a relief for all you Keystone Cops of Databases administration!

In this part of the course, we've gone over how to set up a database, its tables, as well as how to set up the foreign key constraints and indexes.

In the next part, we will dive into SQL to learn how to put info into the database and how to ask it for information, how to update the information and how to erase what we put in.

 

 

 

Example of certificate of achievement
Example of certificate of achievement