Problem

High CPU utilization and CPU memory consumption

The performance plays a vital role in our application to run our online business smoothly, doing  SQL query optimization is tricky, but necessary practice for database professionals. It requires looking at various collections of data using execute the plan, CPU utilization, and CPU memory consumption. Sometimes some queries are expensive to execute. To prevent this some solutions are mentioned below

Don’t Use Distinct

• • Whenever we want a unique record from a query then we can use DISTINCT but it takes too high a cost in terms of CPU utilization, so to prevent this performance issue we should use GROUP BY instead of DISTINCT.

Eg: SELECT <column_name> FROM <table_name> group by <column_name>;

Instead of

Eg: SELECT distinct <column_name> FROM <table_name>;

Use In Operator Instead of the Existing Operator

• • IN operator used to get specify multiple values in a where clause it reduces CPU utilization

Eg: SELECT * FROM <table_name> where <column_name> in (40,50,100,85)

Instead of

Eg: Select * from <table_name> where <column_name> EXISTS (40,50,100,85)

Use Joins with Inner Join not Where

Eg: SELECT <column_name>,<column_name>,<column_name> FROM <table_name> inner join  <table_name> on <table_name><column_name> = <table_name>.<column_name>

Instead of

Eg: SELECT <column_name><column_name>, s<column_name> FROM <table_name>, <table_name> WHERE<column_name> = <column_name>

Recommend to Read:- SQL Query Optimization Tips

Stop Running Queries in a Loop

Eg: INSERT INTO `abc`(col1,col2,col3) VALUES (a,b,d),(x,y,z),(p,q,r) ….

Instead of

        Eg: 
        for ($i = 0; $i < 10; $i++) {
$query = “INSERT INTO abc (col1,col2,col3) VALUES  (a,b,d)”;
$mysqli->query($query);
}

Stop Writing Subquery

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

Instead of

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

Avoid joining with not equal condition

• • When we use the not equal condition operator in join, it scans all rows or scans the full table so to avoid this use join with the not equal operator

Eg: select a.user, b.employee_name, b.manager_name from SALE a join EMPLOYEE b on a.user != b.employee_name

If you are interested in even more Development-related articles and information from us here at Devstringx, then we have a lot to choose from.