How to optimize SQL query and return count using inner join and subquery

Question

We are getting result proper but its taking too much time to execute.Can we optimise query which will take less time to execute.

SELECT c.client_id
FROM client_master c
INNER JOIN user_visit_record u ON u.client_id = c.client_id
WHERE c.dept_id ='1'
  AND c.branch_id='1'
  AND c.client_status IN('Hot','Warm','Cold','Quotation')
  AND u.next_followup_date < '2017-06-01'
  AND u.visit_id IN
    (SELECT max(visit_id)
     FROM user_visit_record
     WHERE client_id=c.client_id)

Thanks in advance.

Probably you get lot more speed if you create indexes for the relevant columns. — Sakura Kinomoto
– Sakura Kinomoto, Commented Jun 1, 2017 at 8:51
Index are at table level, not query level. You need to create index on the table. Example: CREATE INDEX user_visit_record_ix ON user_visit_record (visit_id);. For more info, check mysql manual for indexes here: dev.mysql.com/doc/refman/5.7/en/create-index.html — Sakura Kinomoto
– Sakura Kinomoto, Commented Jun 1, 2017 at 9:23

Kruti Patel · Accepted Answer · 2017-06-01 08:21:26Z

1

Here is the optimized query:

SELECT c.client_id 
FROM client_master c 
JOIN (SELECT * FROM user_visit_record u ORDER BY u.visit_id DESC) AS t ON t.client_id = c.client_id 
WHERE c.dept_id ='1'   
AND c.branch_id='1'   
AND c.client_status IN('Hot','Warm','Cold','Quotation')   
AND t.next_followup_date < '2017-06-01' 
GROUP BY t.clientid

answered Jun 1, 2017 at 8:21

Kruti Patel

1,4722 gold badges24 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vijaya Over a year ago

Thanks You @Kruti Patel ..But this query retrieves wrong result.

Vijaya Over a year ago

Can we get another solutions for this.??

Krunal Limbad · Accepted Answer · 2017-06-01 08:09:14Z

0

I have Used * in query, you can do is select only required columns,one way of optimising

And MySQL has several existing solutions, check this Article

SELECT c.client_id
    FROM 
        (SELECT *
        FROM client_master
        WHERE dept_id ='1'
                AND branch_id='1'
                AND IN('Hot','Warm','Cold','Quotation')) AS c
    INNER JOIN 
        (SELECT *
        FROM user_visit_record
        WHERE next_followup_date < '2017-06-01'
                AND visit_id IN 
            (SELECT max(visit_id)
            FROM user_visit_record
            WHERE client_id=c.client_id)) AS u
            ON u.client_id = c.client_id

answered Jun 1, 2017 at 8:09

Krunal Limbad

1,5831 gold badge13 silver badges24 bronze badges

Comments

Ashraful Islam · Accepted Answer · 2017-06-01 08:27:54Z

0

Try this :

SELECT client_id FROM 
  (
    SELECT client_id 
      FROM client_master 
        WHERE dept_id ='1' AND branch_id='1' AND client_status IN('Hot','Warm','Cold','Quotation')
  ) AS c

  NATURAL JOIN

  (
    SELECT client_id
      FROM user_visit_record 
        INNER JOIN (
          SELECT client_id, max(visit_id) as max_visit 
            FROM user_visit_record
              GROUP BY client_id
        ) AS max_user_visit 
            ON (user_visit_record.client_id = max_user_visit.client_id) 
              AND (user_visit_record.visit_id = max_visit)
        WHERE next_followup_date < '2017-06-01'
  ) AS u

answered Jun 1, 2017 at 8:27

Ashraful Islam

12.9k4 gold badges35 silver badges55 bronze badges

Collectives™ on Stack Overflow

How to optimize SQL query and return count using inner join and subquery

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related