CODEWITHSUNDEEP
phpmysqlsubqueryquery-optimizationbetween
Keerthivasan
Keerthivasan

Reputation: 1661

More than one condition in Mysql Between Condition Checking? Is there any way to optimize this query?

I am Working on report Generating Application, I am generating the query using PHP based on user operations. Now I am query the details based on the experience of the staff members, there is the possibilities for more than one selecting option. The Result is taken from joining 5 tables and sub queries, My Query is Working fine and fetching the exact result

First Scenario : Taking Staff Members between total 15 to 20 Years Experience

Query

SELECT sd.staff_ref_id AS staff_code,
       sd.sur,
       sd.staff_name AS Name,
       sq.degree AS Qualification,
       sd.designation,
       d.department_name,
       TIMESTAMPDIFF(MONTH, s.join_date, CURDATE()) AS rec_exp_months,
       sum(spe.years) AS p_exp_yrs,
       sum(spe.months) AS p_exp_months
FROM staff_details sd
LEFT JOIN
  (SELECT s.staff_id,
          group_concat(sq.degree) AS degree
   FROM staff_details s,
        staff_qualification sq
   WHERE s.staff_id = sq.staff_id
   GROUP BY sq.staff_id
   ORDER BY sq.row_num)sq ON sd.staff_id = sq.staff_id
LEFT JOIN staff_department s ON s.staff_id = sd.staff_id
LEFT JOIN department d ON d.department_id =s.depart_id
LEFT JOIN staff_previous_experience spe ON spe.staff_id = sd.staff_id
WHERE ((TIMESTAMPDIFF(MONTH,sd.join_date,CURDATE())) +
           (SELECT (sum(sqn.years)*12) + sum(sqn.months)
            FROM staff_previous_experience sqn
            WHERE sqn.staff_id=sd.staff_id
            GROUP BY sqn.staff_id) BETWEEN 120 AND 180)
GROUP BY staff_ref_id,
         sd.sur,
         sd.staff_name,
         sd.designation,
         d.department_name,
         sq.degree,
         s.join_date

Second Scenario: Taking staff members between total experience 10 to 15 years and 15 to 20 years Experience

Query

SELECT sd.staff_ref_id AS staff_code,
       sd.sur,
       sd.staff_name AS Name,
       sq.degree AS Qualification,
       sd.designation,
       d.department_name,
       TIMESTAMPDIFF(MONTH, s.join_date, CURDATE()) AS rec_exp_months,
       sum(spe.years) AS p_exp_yrs,
       sum(spe.months) AS p_exp_months
FROM staff_details sd
LEFT JOIN
  (SELECT s.staff_id,
          group_concat(sq.degree) AS degree
   FROM staff_details s,
        staff_qualification sq
   WHERE s.staff_id = sq.staff_id
   GROUP BY sq.staff_id
   ORDER BY sq.row_num)sq ON sd.staff_id = sq.staff_id
LEFT JOIN staff_department s ON s.staff_id = sd.staff_id
LEFT JOIN department d ON d.department_id =s.depart_id
LEFT JOIN staff_previous_experience spe ON spe.staff_id = sd.staff_id
WHERE ((TIMESTAMPDIFF(MONTH,sd.join_date,CURDATE())) +
           (SELECT (sum(sqn.years)*12) + sum(sqn.months)
            FROM staff_previous_experience sqn
            WHERE sqn.staff_id=sd.staff_id
            GROUP BY sqn.staff_id) BETWEEN 120 AND 180
         OR (TIMESTAMPDIFF(MONTH,sd.join_date,CURDATE())) +
           (SELECT (sum(sqn.years)*12) + sum(sqn.months)
            FROM staff_previous_experience sqn
            WHERE sqn.staff_id=sd.staff_id
            GROUP BY sqn.staff_id) BETWEEN 180 AND 240)
GROUP BY staff_ref_id,
         sd.sur,
         sd.staff_name,
         sd.designation,
         d.department_name,
         sq.degree,
         s.join_date

Note: The above highlighted area, i am checking total experience, i am converting both input and result from database as months and checking the conditions

Question : The Darken Area is generated query from php, that i am using the between query..based on how much options selected from experience field, the between query will be automatically generated..
Is There any way to Optimize this..?
Is there an option to use between option in query for several conditions ...?

My Query Execution Time is 0.0663 sec

awaiting Responses, Suggestions and advice's Thanks in Advance

Upvotes: 0

Views: 77

Answers (1)

Rick James
Rick James

Reputation: 142296

It feels like this will give you the same effect as the first WHERE:

WHERE sd.join_date >= CURDATE() - INTERVAL 180 MONTH
  AND sd.join_date  < CURDATE() - INTERVAL 120 MONTH

And have INDEX(join_date).

If so, then it is so much simpler that is must be faster.

The GROUP BY in the highlighted subquery is unnecessary.

The ORDER BY row_num is probably invalid and ignored because of the GROUP BY.

It seems like s is unnecessary in the derived table after the LEFT JOIN.

Why separately compute BETWEEN 120 AND 180 and BETWEEN 180 AND 240? Seems like they could be merged together.

If it is not faster, then make the cleanups I suggested in comments and let's start over. And please provide SHOW CREATE TABLE for each table.

Upvotes: 1

Related Questions