SQL Optimization Techniques️🔨
Found it good to put this up just to share with fellow SQL developers , data enthusiasts and to all those who aspire to delve in the field of SELECTS, INSERTS, UPDATES… what I feel to be on the table and having three years experience interacting with MySQL database.
To begin with, SQL query optimization refers to writing thoughtful queries that are less of a performance bottleneck to a database architecture. Note that a minor change may have a positive dramatic impact on the performance of a database infrastructure often in cases where there are intense write operations.
Here are some of the techniques I obtained through personal experience with MySQL and by wading through numerous pages over the web to sample them and generate a consumable summary. The sampled techniques below, I hope, will be an impact to the optimality of your basic to advanced queries while working through your day to day queries.
NOTE: The examples given herein for illustrations are as per the MySQL syntax but that does not interfere with the concept.
1. Indexing for a quick access
Indexing is a data structure technique which allows you to quickly retrieve records from a database file and is a small table of two columns. The first column comprises a copy of the primary or candidate key of a table. Its second column contains a set of pointers for holding the address of the disk block where that specific key value stored.
Indexing in SQL Server helps retrieve data more quickly from a table, thereby giving a tremendous boost to SQL query performance. Allow effective use of clustered and non-clustered indexes. Understand the query’s intent and choose the right form for your scenario.
Proper indexing ensures quicker access to the database, i.e. you’ll be able to select or sort rows faster.
Read more about database indices on database index explained
2.Using SELECT queries.
Always avoid selecting all queries using the operator * instead retrieve only the necessary fields.
Example of inefficient use: SELECT * FROM Students
Efficient use: SELECT student_name,student_adm FROM student
3. Selecting rows.
Avoid the use of HAVING clause for primary filters but instead use WHERE clause.
HAVING statements are determined in the SQL operating order after WHERE statements. Therefore, it is more efficient to execute the WHERE query than HAVING .
4.Minimize loops in query .
Executing loops always consumes a lot of system resources, the iterations are executed one at a time.
Inefficient: for ($i = 0; $i < 10; $i++) { $query = “INSERT INTO TBL (A,B,C) VALUES . . . .”; $mysqli->query($query); printf (“New Record has id %d.\ “, $mysqli->insert_id); }
Efficient: INSERT INTO TBL (A,B,C) VALUES (1,2,3), (4,5,6). . . .
Instead consider using BULK INSERT or using a temporary table for dumping massive data into the database.
5.Subqueries.
Avoid the use of correlated subqueries since the execution of the inner query depends on the parent query hence searches the entire table row by row before returning the results.
This is quite common for most developers when using a WHERE clause instead of an INNER JOIN. Take a look at this:
SELECT c.Name, c.City,(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c instead of SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID
This is quite common for most developers when using a WHERE clause instead of an INNER JOIN. Take a look at this:
SELECT c.Name, c.City,(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c instead of SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID
6.Matching Records.
Consider using EXISTS() function to check whether an element exist instead of using the COUNT() function. EXISTS() terminates the table scan immediately a column matches while a COUNT() will count all the rows in a given table.
Inefficient: IF (SELECT COUNT(1) FROM EMPLOYEES...
Efficient: IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES...
7. Use wildcards sparingly and cautiously
This may be much tied up to the kind of the result set of the query executed. Consider a query that should retrieve all names ending with ‘on’ using two scenario of wildcards %on% and %on.
