CODEWITHSUNDEEP
phpmysqlsqloptimizationexists
Metin Erbek
Metin Erbek

Reputation: 84

EXISTS query optimization on mysql query

I have a big data problem with MySQL.

I have:

But when I search which users have user note in some dates.

My query like this : 

SELECT u.*, rep.name as rep_name FROM users as u
LEFT JOIN users as rep on rep.id = u.add_user 
LEFT JOIN authorization on authorization.id = u.authorization 
LEFT JOIN user_situation_list on user_situation_list.user_situation_id = u.user_situation 
WHERE 
    EXISTS(
    select * from user_notes 
    where user_notes.note_user_id = u.id AND user_notes.create_date 
    BETWEEN "2017-10-20" AND "2017-10-22"
    )
ORDER BY u.lp_modify_date DESC, u.id DESC

Upvotes: 0

Views: 1006

Answers (2)

Rick James
Rick James

Reputation: 142208

Turn it around -- find the ids first; deal with the joins later.

SELECT  u.*, 
        ( SELECT  rep.name
            FROM  users AS rep
            WHERE  rep.id = u.add_user ) AS rep_name 
    FROM  (
        SELECT  DISTINCT note_user_id
            FROM  user_notes
            WHERE  create_date >= "2017-10-20"
              AND  create_date  < "2017-10-20" + INTERVAL 3 DAY 
          ) AS un
    JOIN  users AS u  ON u.id = un.note_user_id
    ORDER BY  lp_modify_date DESC, id DESC

Notes

  • No GROUP BY needed;
  • 2 tables seem to be unused; I removed them;
  • I changed the date range;
  • User notes needs INDEX(create_date, note_user_id);
  • Notice how I turned a LEFT JOIN into a subquery in the SELECT list.

If there can be multiple rep_names, then the original query is "wrong" in that the GROUP BY will pick a random name. My Answer can be 'fixed' by changing rep.name to one of these:

MAX(rep.name) -- deliver only one; arbitrarily the max

GROUP_CONCAT(rep.name) -- deliver a commalist of names

Upvotes: 1

Michael Kunst
Michael Kunst

Reputation: 2988

Rewriting your query to use a JOIN rather than an EXISTS check in the where should speed it up. If you then group the results by the user.id it should give you the same result:

SELECT u.*, rep.name as rep_name FROM users as u
LEFT JOIN users as rep on rep.id = u.add_user 
LEFT JOIN authorization on authorization.id = u.authorization 
LEFT JOIN user_situation_list on user_situation_list.user_situation_id = u.user_situation 
JOIN user_notes AS un 
  ON un.note_user_id
  AND un.create_date BETWEEN "2017-10-20" AND "2017-10-22"
GROUP BY u.id
ORDER BY u.lp_modify_date DESC, u.id DESC

Upvotes: 0

Related Questions