Top 05 SQL Query Optimization Tips | Devstringx

SQL Query optimization is very important to run your project smoothly. By using these tips we can reduce the Query execution time, Unwanted DB calls and Queries, Reduce DB load.

Query Optimization help to handle the Big data in your application. Below are some points to be remembered.

1. Indexing
2. Select Query
3. Running Queries
4. Fetching Data
5. Sub Queries

1. Indexing:

Indexing is used to search the data more efficiently/Quickly from the database. Users can create the index key but can’t see the internal process of data indexing. Indexing is the internal process of the database to find a record more quickly from the database.

CREATE INDEX index_name

ON table_name (column1, column2, …);

Note: Indexing reduces the update Query speed. Because it needs to update the indexing of that table also.

2. Select query:

Select the specific column from the database instead of selecting All. more data makes slow take more space of memory and it affects the complete database, not the single query.

Id Book_Name Author Description Qty Sold_Stock Current_Stock Price Date

Inefficient Query

Select * form Books

Efficient Query

Select id,book_name from the Books

3. Running queries:

Execution of query in the loop is an inefficient way of working it can stick the database in case of large database handling. To insert and update the data in the loop we should use the bulk insert or update query. That will improve the database performance.

Inefficient way

foreach($data as $row){

$query = “INSERT INTO books (A,B,C) VALUES ($row[0],$row[1],$row[2])”;

}

Efficient way

$query = “INSERT INTO TBL (A,B,C) VALUES (1,2,3),(1,2,3),(1,2,3),(1,2,3)”;

4. Fetching data:

Fetch the limited data from the database with the proper conditioning. A large no of rows takes more time to be found. Retrieving fewer data will also reduce the client-side data execution time.

5. Subqueries:

Subqueries or correlated queries are not good for database performance. As the inner query execute row by row for every parent record. So that it increase the database execution time and memory space consumption.

Example:

SELECT B.Name, B.id,(SELECT nameFROM user WHERE user .ID = B.author) AS authorName FROM Book B

The above query is not efficient as the inner query will search all records from the user table for one row of the book table. We can use the join to reduce this performance issue.

Originally published at https://www.devstringx.com on Jan 03, 2022.