MySQL Forums
Forum List  »  Performance

Re: Optimisation of a query using Group by and a Join
Posted by: Peter Brawley
Date: October 19, 2015 02:10PM

MySQL tends to optimise in( select ...) poorly; the query will probably perform better if you change that to a Join. And overall, the query is easier to understand in explicit join syntax. So we get ...

update orders 
join (
  select id, c.email, t1.needed 
  from customers c 
  join (
    select min(id) as needed, email 
    from customers 
    group by email 
    having count(*) > 1
  ) t1 on c.email = t1.email 
  join (
    select email 
    from customers 
    group by email 
    having count(*) > 1
  ) x on t1.email=x.email
) t2 on customerid = t2.id 
set customerid = t2.needed ;

But do you need that Where clause or Join at all? ...Group By Email having Count(*)>1... in t1 already restricts the resultset to customers with multiple emails.

To find if your Where clause is redundant, compare the result of ...

select orders.customerid
join (
  select id, c.email, t1.needed 
  from customers c 
  join (
    select min(id) as needed, email 
    from customers 
    group by email 
    having count(*) > 1
  ) t1 on c.email = t1.email 
  where c.email in (
    select email 
    from customers 
    group by email 
    having count(*) > 1
  )
) t2 on orders.customerid = t2.id ;

with ...

select orders.customerid
join (
  select id, c.email, t1.needed 
  from customers c 
  join (
    select min(id) as needed, email 
    from customers 
    group by email 
    having count(*) > 1
  ) t1 on c.email = t1.email 
) t2 on orders.customerid = t2.id ;

When you get that sorted, if your MySQL version is 5.6.3 or later, run Explain Extended on your update query to see what indexes it is using. If your version is earlier than 5.6.3, run Explain Extended on the Select version of your query. First indexes to try will likely be a customers index on (email,id), and an orders index on customerid.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimisation of a query using Group by and a Join
730
October 19, 2015 02:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.