CODEWITHSUNDEEP
mysql
Alex
Alex

Reputation: 3958

Why is this query so slow and what can i do about it

I have the following SELECT UPDATE statement from MySQL

UPDATE table_Learning l
INNER JOIN (select ULN, id from table_users group by ULN having count(ULN) =1) u 
ON l.ULN = u.ULN
set l.user_id=u.id
WHERE l.user_id is null

The problem is, it is so slow that it times out, and basically does not work.

I am sure it is to do with the line:

INNER JOIN (select ULN, id from table_users group by ULN having count(ULN) =1) u

and specifically because there is both a GROUP BY and a HAVING clause in this inner select, and from what I have read, because INNER JOINS are very slow with MySQL.

My overall aim is to:

Populate the userID's that are null in table_learning To do so using the userID's in table_users To Join on the field named ULN in both tables To only populate the fields where the ULN is unique in table_users eg if more than one user has this ULN, then do not populate the user_id in table_learning

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This is your query:

UPDATE table_Learning l INNER JOIN
       (select ULN, id
        from table_users
        group by ULN
        having count(ULN) = 1
       ) u 
       ON l.ULN = u.ULN
    set l.user_id=u.id
    WHERE l.user_id is null;

In MySQL, the subquery is going to be expensive. An index on table_learning(user_id) might help a bit. But filtering inside the subquery could also help:

UPDATE table_Learning l INNER JOIN
       (select ULN, id
        from table_users
        where exists (select 1
                      from table_learning tl
                      where tl.ULN = u.uln and tl.user_id is null
                     )
        group by ULN
        having count(ULN) = 1
       ) u 
       ON l.ULN = u.ULN
    set l.user_id=u.id
    WHERE l.user_id is null;

For this, you want a composite index on table_learning(ULN, user_id).

Upvotes: 2

Related Questions