How SQL DB server works

In the previous chapter, we set up our sandbox table sample_table with 4 columns and 50 million rows. Now, we will execute a few queries to understand how SQL databases retrieve the rows we request.

Connect to the DB

Initial exploration

Let’s take a look at what we are dealing with. We know we have entry_date in the table sorted in ascending order.

Start MySQL profiling

Query 1: Retrieve just the first day

We know that the first day we have data for is at the top of the table.

It takes around 17 seconds.

The DBI library performed commit DB commands after the query, and again, after the SHOW PROFILES command.

If you are using a GUI client to connect to your DB, it may show a time for the query to execute. In MySQL Workbench, this would be shown as Duration/Fetch. And for this query, MySQL Workbench shows 0.029 sec / 17.224 sec. The sum of these is roughly the same as the duration shown in the profiles result.

The majority of the time is spent in executing.

Query 2: Retrieve just the last day

Let’s do the same for the last day we have, 2023-12-31. We know that the last day of the data we have is at the end of the table.