RUBICTRON: August 2018

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);

RUBICTRON

Pages

Tuesday, August 14, 2018

MySQL performance tuning

Good practicess in writing sql quary