Index Number Record For Free

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...