In this article, we will discuss how to increase the speed of SQL queries while still keeping SQL statements concise. Before we get into the topic, let's take a look at how queries are actually processed:

1. Query Processing

Query processing is defined as the process of extracting data from a database through a series of processes. This involves converting the SQL statement's into a form that the database can understand and querying the final result.

Query processing involves three main steps:

1. Parsing and Translation: Query processing begins with the parsing and translation of SQL. Similar to a parser in a compiler, a parser examines the query syntax to see 

   if the references are in the database.SQL is a high-level query language that needs to be translated into relational expressions.

2. Optimization: SQL queries can be written in many different ways. Optimized queries also depend on how the data is stored in the file organization. A Query can also 

   have different relational expressions to correspond to it.

3. Execution Plan: An execution plan consists of the systematic step-by-step execution of basic operations to fetch data from the database. For a particular query, 

   different evaluation plans have different query costs. This includes the number of disk accesses, CPU time to execute the query, communication time in the case of 

   distributed databases, and so on.

2、 SQL query optimization

SQL query optimization is defined as the process of enhancing and accelerating query performance between execution time, number of disk accesses, and more cost. Data should be accessed in the fastest way possible to enhance the user experience when using the application.

The purpose of SQL query optimization:

1. Reducing response time: The main goal is to improve performance by reducing response time. The time difference between the user requesting data and getting a 

   response should be minimized for a better user experience.

2. Reduce CPU Execution Time: The CPU execution time of the query must be reduced to get the results faster.

3. Increase throughput: The number of resources that need to be accessed to get all the necessary data should be minimized.

3、Common SQL query optimization techniques

3.1 Using SELECT field names instead of SELECT *

Get only the necessary data from the table, not all of it. For example:

SELECT * FROM Business

A more efficient way to write queries is:

SELECT name , age , gender FROM Business

This query is much simpler and extracts only the required details from the table.

3.2 Avoid using DISTINCT in SELECT as much as possible

SELECT DISTINCT is a simple way to remove duplicates from the database, or you can generate different results by combining it with the GROUP BY clause, which groups all fields in the query. However, doing this consumes a lot of processing power. Therefore, avoid DISTINCT in SELECT queries.

3.3 Proper use of indexes

Proper use of indexes can reduce the execution time of common statements. For example:

CREATE INDEX index_optimizer ON Business(id);

3.4 To check whether a record exists, use EXISTS instead of COUNT

Both the EXISTS() and COUNT() methods can be used to check for the existence of record entries in a table. the EXISTS() method is more efficient because it quits processing once it finds the first entry of a record in the table. the COUNT() method scans the entire table to return the number of records in the table that match the constraints provided. the COUNT() method scans the entire table to return the number of records in the table that match the constraints supplied.

For example:

SELECT count(id) FROM Business

A more effective way to write it would be:

EXISTS (SELECT (id) FROM Business)

3.5 Limiting the result set size using limit

The less data retrieved, the faster the query runs.

3.6 Try to use WHERE instead of HAVING.

The HAVING clause filters the rows after selecting all of them.The HAVING statement comes after the WHERE statement in determining the order of SQL operations. As a result, it is faster to execute WHERE queries.

For example:

SELECT c.ID, c.CompanyName, b.CreatedDate FROM Business b
JOIN Company c ON b.CompanyID = c.ID
GROUP BY c.ID, c.CompanyName, b.CreatedDate
HAVING b.CreatedDate BETWEEN ‘2020-01-01’ AND ‘2020-12-31’

A more effective way to write:

SELECT c.ID, c.CompanyName, b.CreatedDate FROM Business b
JOIN Company c ON b.CompanyID = c.ID
WHERE b.CreatedDate BETWEEN ‘2020-01-01’ AND ‘2020-12-31’
GROUP BY c.ID, c.CompanyName, b.CreatedDate

3.7 Ignoring link subqueries

The link subquery depends on the query from the parent or from an external source. It runs line by line, so the average loop speed is greatly affected. 

For example:

SELECT b.Name, b.Phone, b.Address, b.Zip, (SELECT CompanyName FROM Company WHERE ID = b.CompanyID) AS CompanyName FROM Business b

SELECT b.Name, b.Phone, b.Address, b.Zip, c. CompanyName FROM Business b
Join Company c ON b.CompanyID = c.ID

4. Summary

In this article, we covered some techniques for optimizing SQL queries. Usually, the factor that has the biggest impact on query speed is the proper use of indexes. We hope that the content in this article can help you.