Today we are going to run SQL queries against a table containing ten THOUSAND records.
{{ Maniacal laughter }}
{{ Phone call }}
What is it, I’m in the
middle of a video…
You don’t say?
ALL in RAM?…
Well, alrighty then...
Today we are going to run SQL queries against a table containing one .. Hundred .. MILLION
records.
{{ Maniacal laughter }}
But don’t worry.
By using indexes, we can rapidly speed up queries so you do not have to experience the
phenomenon known as boredom.
We will work with a single table called ‘person’ containing 100 MILLION randomly generated people.
The first row is an auto-generated primary key called “person_id”
The other columns are first_name… last_name… and birthday.
To create this table, we randomly generated names using the 1000 most popular female names,
male names, and last names in the United States.
We did not weight the names by frequency when generating our random sample.
The datasets and the Python code used to generate the random names are available for download
from Github.
Today, we are going to pay close attention to how long each query takes to execute.
So on the right, we will maintain a list of queries and how many milliseconds each query
took to complete.
These values will vary depending on the specifications of your workstation.
We will begin by counting the number of rows in the “person” table…
There are indeed 100 MILLION records.
Notice how long it took to perform this simple count.
3 seconds and 562 milliseconds.
Or 3,562 milliseconds.
Let us put this query and the time required in our table.
As another speed test, count the number of people with the last name “Smith.”
Execute
Over 100,000 Smiths...
And the query took 4,261 milliseconds.
For a second test, count how many people are named “Emma.”
Execute…
Nearly 50,000.
And note the time: 4,066 milliseconds.
What if we count the number of people born in May, 1980?
For this we will use the BETWEEN operator.
Execute…
166,000...
And the query took 4,480 milliseconds.
A pattern is starting to emerge.
Let’s quickly gather a few more speed samples.
How many Michaels are in our database?
4,127 milliseconds
How many people have the last name ‘Hawkins’ or ‘Snow’?
5,315 milliseconds
And how many people were born on April 1, 1995?
4,110 milliseconds.
We ran 7 queries, and the execution times were all very similar.
This is because in order to answer each query, the database had to scan all 100 MILLION rows
to check each record for a match.
Our 7 scans of the entire table took a combined total of 29,921 milliseconds.
This means on average my workstation was able to scan 23,394,940 rows per second.
While this may seem fast, we can do better…
MUCH better…
And we will do this by building an INDEX.
The name ‘index’ was chosen because of how an index works in a book.
For example, if you want to learn about “normal distributions”, you would not flip through
your entire math book page by page, scanning for the term “normal distribution.”
Instead, you would go to the index, find the term “normal distribution”, and you would
see the list of pages.
This technique is used by databases.
When you create an index, the database will generate a method to rapidly find data based
on one or more columns.
As our first example, let us create an index on the “person” table.
To do this, write “CREATE INDEX” and then give your index a name.
There are many different naming conventions that you can use.
The important thing is to be consistent.
We will use the table name, followed by the field name, then add “idx” to indicate
this is an index.
Next, write “ON” followed by the table name.
Inside parentheses, write the columns to include in the index.
We will start simply and create an index for the “first_name” column.
Execute.
That certainly took a while.
Remember, the database had to scan 100 MILLION rows and build a “first name” index from scratch.
Let’s test our new index by repeating an earlier query where we counted the number
of people named “Emma.”
Execute.
This time, it took only 508 milliseconds.
If we repeat the count of people with last name “Smith”, it takes 4,415 milliseconds.
What’s going on here?
This is about as long as it took last time.
It did not speed up because our index was built for the first name column.
The last name was not indexed.
And as you might suspect, if we search for people born on April 1, 1995, the query should
not run any faster than last time.
Execute.
4,011 milliseconds.
No real change.
Our index improved the speed of searches by first name, but had no effect on queries by
last name or birthday.
However, there are other queries where the “first name” index will improve performance.
For example, count the number of people named “Julie Andrews.”
Execute.
This ran in just 514 milliseconds.
By contrast, if you count people with last name ‘Andrews’ and born on June 12, 1992...
It takes 4,274 milliseconds.
The full name query benefited from the first name index, because the database was able
to use it to quickly find all the “Julies.”
From this smaller set of records, it then completed the job by finding people with last
name ‘Andrews’.
The second query, however, had no index to help.
It had to perform a full scan of the table to find all people with last name “Andrews”,
and then find those with a matching birthday.
Fortunately databases are clever about how to use indexes.
For example, if you count the number of people born on October 31, 1985 with a first name
of “Mia”...
It executes quickly: 512 milliseconds...
But why?
Wouldn’t the database first scan all rows to find people with a matching birthday, and
then filter by first name?
No!
Just because we wrote it in this order does not mean the database will do its work in
that order.
The database will consider all possible ways to execute your query, then chooses the optimal path.
We call each possibility a “query plan”, and the “query optimizer” picks what it
thinks is best.
You’ll be happy to know a database can have more than one index.
Better still, each index can be created using more than one column.
Let us create a multi-column index.
Write “CREATE INDEX”, then an overly long but descriptive name.
Next, write “ON” then the table name.
In parentheses, list the columns you want to go into this index.
Important Note: The order matters.
Think of this as sorting the data first by last_name, then by first_name
Execute…
It is now time to take this new index for a test run.
How many people named “John Williams” are in the database?
Execute…
There are 36 people.
And it only took 27 milliseconds to run this query.
What about Julie Andrews?
26 milliseconds.
Well done, index..
Well done...
A word of caution.
Indexes are not free.
Just as indexes in a book require paper, indexes in a database require storage.
Furthermore, when you add data to a database, it needs to create new records AND update
all relevant indexes.
So go forth and use indexes, but use them wisely.
And before you go, please help us achieve our goal of
being the first channel with One Hundred BILLION subscribers!
{maniacal laughter hahhahaa please subscribe, really}