MySQL performance tuning

MySql there can some tunning for get fast performance in ths post have shown what are the mistakes and how to solve it to get best performance.

visit MySQL quary part 1
visit MySQL quary part 2
visit MySQL quary part 3
visit MySQL quary part 4
visit MySQL quary part 5

Good practicess in writing sql quary

• Use single case for all SQL verbs
• Begin all SQL verbs on a new line
• Separate all words with a single space
• Right or left aligning verbs within the initial SQL verb

Use the actual columns names in SELECT statement instead of *

Slow

---

  SELECT *
  FROM student

---

Fast

---

  SELECT id,name,address,age,semestor
  FROM student

---

Use non-column expression on one side of the query

Slow

---

  SELECT id,name,address,age,semestor
  FROM student
  WHERE age-5 > 10

---

Fast

---

  SELECT id,name,address,age,semestor
  FROM student
  WHERE age > 15

---

Whenever possible try not to use the NOT logical operator.

Slow

---

  SELECT id,name,address,age,semestor
  FROM student
  WHERE NOT age > 10

---

Fast

---

  SELECT id,name,address,age,semestor
  FROM student
  WHERE age <= 10

---

When using multiple conditional

• expressions write the equality condition last. They are much faster to evaluate.
• multiple AND conditions write the conditions that most likely to fail last
• multiple OR conditions write the condition that most likely to be true last

  Example
  female student -- 25%
  male student -- 75%
  semester 5 student -- 60%
  semester 4 student -- 40%

Slow

---

  SELECT id,name,semester,sex
  FROM student
  WHERE sex = 'F'
  AND semester = 5
  

  SELECT id,name,semester,sex
  FROM student
  WHERE semester = 5
  OR sex = 'F'

---

Fast

---

  SELECT id,name,semester,sex
  FROM student
  WHERE semester = 5
  AND sex = 'F'
  

  SELECT id,name,semester,sex
  FROM student
  WHERE sex = 'F'
  OR semester = 5

---

Try to minimize the number of subquery block in main query.

Slow

---

  SELECT name
  FROM student
  WHERE grade  = (SELECT MAX(grade)
                  FROM studentDetails)
  AND semester = (SELECT MIN(semester)
                  FROM studentDetails)
  

---

Fast

---

  SELECT name
  FROM student
  WHERE (grade, semester )
    = ( SELECT MAX(grade),MIN(semester)
        FROM studentDetails)

    

---

Be careful while using conditions in WHERE clause.

• Avoid redundant conditions in the where clause
• Avoid concatenation in the where clause
• Avoid redundant expressions in the where clause
• Numeric field comparisons are faster than character, date and null
• Equality comparisons are faster than inequality comparisons

Slow

---

  SELECT id,name,sex
  FROM student
  WHERE sex = 'F'
  AND sex <> 'M'

  SELECT id, name, salary
  FROM employee
  WHERE dept || location= 'ElectronicsGalle'


  SELECT id,name,sex
  FROM student
  WHERE UPPER(name) LIKE 'ABCD'

  SELECT id, first_name, age
  FROM student_details
  WHERE SUBSTR(first_name,3,2) = 'an'

  SELECT *
  FROM student
  WHERE age >= 10
  AND age <= 20

---

Fast

---

  SELECT id,name,sex
  FROM student
  WHERE sex = 'F'


  SELECT id, name, salary
  FROM employee
  WHERE dept = 'Electronics'
  AND location = ‘Galle';

  SELECT id,name,sex
  FROM student
  WHERE name LIKE 'ABCD'

  SELECT id, first_name, age
  FROM student_details
  WHERE first_name LIKE '__an%'

  SELECT *
  FROM student
  WHERE age BETWEEN 10 and 20

    

---

HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.

Slow

---

  SELECT subject, count(subject)
  FROM student_details
  GROUP BY subject
  HAVING subject != ‘Science'
  AND subject != ‘Maths';
  

---

Fast

---

  SELECT subject, count(subject)
  FROM student_details
  WHERE subject != 'Science'
  AND subject != 'Maths'
  GROUP BY subject;
    

---

Use EXISTS instead of DISTINCT when using joins which involves tables having one-tomany relationship

Slow

---

  SELECT DISTINCT d.dept_id, d.dept
  FROM dept d,employee e
  WHERE e.dept = e.dept;


  

---

Fast

---

  SELECT d.dept_id, d.dept
  FROM dept d
  WHERE EXISTS (SELECT 'X'
                FROM employee e
                WHERE e.dept = d.dept);
    

---

Use operator EXISTS, IN and table joins appropriately in your query.

• Usually IN has the slowest performance
• IN is efficient when most of the filter criteria is in the sub-query.
• EXISTS is efficient when most of the filter criteria is in the main query.

Slow

---

  SELECT s.id,s.name,s.semester,d.id,d.name
  FROM student s,department d
  WHERE s.id=d.id

  

  SELECT *
  FROM product p
  WHERE product_id IN
  (SELECT product_id FROM order_items);

---

Fast

---

  SELECT s.id,s.name,s.semester,d.id,d.name
  FROM student s
  INNER JOIN department
  ON s.id=d.id
  

  SELECT *
  FROM product p
  WHERE EXISTS (SELECT 'x' 
    FROM order_items o
    WHERE o.product_id = p.product_id);

---