SQL Query optimization

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, and 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 * from Books

Efficient Query

Select id,book_name from the Books

3. Running Queries

Execution of queries in the loop is an inefficient way of working it can stuck to 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)”;

Read Also:- All About HTML Injection and Attack

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 executes row by row for every parent record. So that it increases 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.