In a Snap

Log into cs50.dev, click on your terminal window, and execute cd by itself. You should find that your terminal window’s prompt resembles the below:

$

Next execute

wget https://cdn.cs50.net/sql/2024/x/psets/5/snap.zip

in order to download a ZIP called snap.zip into your codespace.

Then execute

unzip snap.zip

to create a folder called snap. You no longer need the ZIP file, so you can execute

rm snap.zip

and respond with “y” followed by Enter at the prompt to remove the ZIP file you downloaded.

Now type

cd snap

followed by Enter to move yourself into (i.e., open) that directory. Your prompt should now resemble the below.

snap/ $

If all was successful, you should execute

ls

and see a database named snap.db alongside a set of .sql files. If not, retrace your steps and see if you can determine where you went wrong!

The users table contains the following columns:

• id, which is the user’s ID.
• username, which is the user’s username.
• phone_number, which is the user’s phone number.
• joined_date, which is the date the user joined the app.
• last_login_date, which is the date the user last logged in.

The friends table contains the following columns:

• user_id, which is the ID of a given user.
• friend_id, which is the ID of the user with whom the given user is friends.
• friendship_date, which is the date the friendship began.

For each row, the user in the user_id column counts the user in the friend_id column among their friends—but not necessarily vice versa. When two users both count each other among their friends, two rows are inserted:

The messages table contains the following columns:

• id, which is the ID of the message.
• from_user_id, which is the ID of the user who sent the message.
• to_user_id, which is the ID of the user to whom the message was sent.
• picture, which is the filename of the picture that was sent in the message.
• sent_timestamp, which is the timestamp at which the message was sent.
• viewed_timestamp, which is the timestamp at which the message was viewed.
• expires_timestamp, which is the timestamp at which the message expires.

Recall that, in SQL, you can use certain keywords to find the intersection of two groups. See Week 1’s notes for a refresher.

To check the results of EXPLAIN QUERY PLAN, you need simply prepend EXPLAIN QUERY PLAN to your query:

EXPLAIN QUERY PLAN
SELECT "username"
FROM "users"
WHERE "id" = 151;

Recall from lecture that EXPLAIN QUERY PLAN displays the steps the SQLite database engine will take to execute a given SQL query. The output of EXPLAIN QUERY PLAN can indicate whether a query is utilizing an index.

• If you see a step labeled as USING INDEX, it signifies that the query is leveraging an index in that step.
• If you see a step labeled as USING COVERING INDEX, it indicates that the query is using a covering index in that step.
  • Recall that a covering index is a special type of index that includes all the columns needed for the query. This means the database can fulfill the query directly from the index without having to look up additional data in a table.
• When you see a step labeled as USING INTEGER PRIMARY KEY, it implies that the query is utilizing the index on the primary key column, which is provided automatically by SQLite when the primary key is of the INTEGER type affinity. It is an efficient way to access rows directly if the query conditions involve a table’s primary key.

Below are some examples of the results of EXPLAIN QUERY PLAN, from queries that use an index in at least one step of their execution:

Example 1

QUERY PLAN
`--SEARCH users USING INDEX search_users_by_last_login (last_login_date>?)

Notice that this query can be executed in a single step, by searching the index search_users_by_last_login.

Example 2

QUERY PLAN
|--SEARCH messages USING COVERING INDEX search_messages_by_to_user_id (to_user_id=?)
`--SCALAR SUBQUERY 1
   `--SEARCH users USING COVERING INDEX sqlite_autoindex_users_1 (username=?)

Notice that this query requires two steps:

1. The first searches the index search_messages_by_to_user_id.
2. The second resolves a subquery by searching the index sqlite_autoindex_users_1.

Example 3

QUERY PLAN
|--SEARCH messages USING INDEX search_messages_by_from_user_id (from_user_id=?)
|--SCALAR SUBQUERY 1
|  `--SEARCH users USING COVERING INDEX sqlite_autoindex_users_1 (username=?)
|--USE TEMP B-TREE FOR GROUP BY
`--USE TEMP B-TREE FOR ORDER BY

Notice that this query involves several steps, and that it uses indexes to accomplish most:

1. The first step searches the index search_messages_by_from_user_id.
2. The second step searches the index sqlite_autoindex_users_1.
3. The final steps use temporary B-trees to group and order the results.

Example 4

QUERY PLAN
|--SEARCH users USING INTEGER PRIMARY KEY (rowid=?)
`--SCALAR SUBQUERY 1
   |--SCAN messages USING COVERING INDEX search_messages_by_to_user_id
   `--USE TEMP B-TREE FOR ORDER BY

Notice that this query involves several steps, and that it uses indexes to accomplish most:

1. The first step searches an automatic primary key index.
2. The second step scans rows using the index search_messages_by_to_user_id.
3. The final step uses a temporary B-tree to order the results.

Example 5

QUERY PLAN
`--COMPOUND QUERY
   |--LEFT-MOST SUBQUERY
   |  |--SEARCH friends USING COVERING INDEX sqlite_autoindex_friends_1 (user_id=?)
   |  `--SCALAR SUBQUERY 1
   |     `--SEARCH users USING COVERING INDEX sqlite_autoindex_users_1 (username=?)
   `--INTERSECT USING TEMP B-TREE
      |--SEARCH friends USING COVERING INDEX sqlite_autoindex_friends_1 (user_id=?)
      `--SCALAR SUBQUERY 3
         `--SEARCH users USING COVERING INDEX sqlite_autoindex_users_1 (username=?)

Notice that this query involves many steps, and that it uses indexes to accomplish most. See in particular that the index sqlite_autoindex_friends_1 is frequently accessed.