DB Design #4 - Select Query and its performance

Database efficiency and optimization mainly depends on how fast our database is able to retrieve the required information

One of the most essential and powerful aspects of SQL is the SELECT statement, which allows us to retrieve and manipulate data efficiently. Whether you're a beginner or an experienced database professional, understanding SELECT queries is crucial for database architecture.

When we design our database, we have to consider the possible use cases for fetching our data beforehand for efficiency.
Understanding how SELECT queries work is crucial for optimizing our DB design.

The previous article in the DB design series covered SQL keys and how it is used to establish relationships between multiple tables.

In this article, we’ll discuss SELECT queries and its components. I’ll provide some useful resources to learn more about these topics at the end of this article.

Keep reading till the end of the article, where I'll give some valuable insights and strategies for enhancing the performance of your SELECT queries.

SELECT queries

We'll start by exploring the basic structure of a SELECT query and its components.

Basics of SELECT Queries:

Let's consider our "Customers" table with the following columns: "CustomerID," "FirstName," "LastName," "Email," and "Phone."

To retrieve all columns from the "Customers" table, the SELECT query would look like this:

SELECT * FROM Customers;

The asterisk (*) is a wildcard that represents all columns in the table. This query will return all rows and columns from the "Customers" table.

Retrieving Specific Columns

In many cases, you may not need all the columns from a table. By specifying the desired columns in the SELECT statement, you can retrieve only the information you need. For example, to retrieve just the "FirstName" and "LastName" columns from the "Customers" table, the SELECT query look like:

SELECT FirstName, LastName FROM Customers;

This query will return all rows but only the "FirstName" and "LastName" columns, making the result set more focused and concise.

---

Filtering results with WHERE Clause:

Often, you'll need to filter data based on specific conditions. The WHERE clause allows you to specify conditions to narrow down the result set. For example, to retrieve only the customers whose first name is "John," you can use the following query:

SELECT * FROM Customers WHERE FirstName = 'John';

This query will return all columns for customers whose first name is exactly "John." The WHERE clause acts as a filter, retrieving only the rows that meet the specified condition.

Remember, this is just the tip of the iceberg when it comes to SELECT queries. SQL offers a wide range of operators, functions, and clauses to refine and manipulate data retrieval.

---

Resources:

• SQL joins to understand how we use select queries and retrieve data from multiple tables by joining them
  https://www.edureka.co/blog/sql-joins-types
  Written by Sahiti Kappagantula

• SELECT query and its components in detail
  https://www.freecodecamp.org/news/sql-select-statement-and-query-examples/
  Written by Joel Olawanle

---

Performance of our SELECT query

By going through the basics of SELECT query, you might think that this is simple and easy to understand.

The real task is to understand how our select query works and how fast it retrieves data from our database.

Let me show you a small example to help you understand this better.

We’ll consider our Customers table with 122 rows and we’ll query one particular row.

Scenario 1:

I’m fetching the information for a customer with the first name “Susan”.

SELECT * from Customers where FirstName = "Susan";

Query result

Analysis of the select queryw

The above image shows how our select query searched our table for the record and fetched the result.

Note that the “rows” entry in the result shows that this query has scanned 122 rows, i.e. all rows in the table, to get one entry.

Scenario 2;

I’m fetching the information for a customer with the customer ID “124”.

SELECT * from Customers where customerID = 124;

Query result

Analysis of the select query

Note that in our 2nd scenario, the “rows” entry shows that our SELECT query has scanned only 1 row to fetch our result.

It is obviously faster to scan 1 row than to scan the entire table to fetch the same result, isn’t it?

This might seem to occur very quickly in our simple table.

When we are dealing with enterprise level databases with terabytes of data, these changes will heavily impact our database performance.

How to analyse our SELECT query?

We can analyse our SELECT query using the EXPLAIN STATEMENT in SQL.

There are many more features that comes along with EXPLAIN STATEMENT and we’ll discuss those in the next post of our DB design series.

The next couple of posts in our DB Design series will contain highly valuable information about query optimization and performance.

Stay tuned to Full-Stack newsletter to get instant updates.

Until next time!