mysql - Optimize sql query to get duplicates -

a have following sql query:

select users.* users users  users.name <> '' , users.email <> '' , users.phone <> ''  , users.name in (  select name             users                 name <> '' , name not null             group name             having count(name) > 1 ) , users.email in (  select email             users                 email <> '' , email not null             group email             having count(email) > 1 ) , users.phone in (  select phone             users                 phone <> '' , phone not null             group phone             having count(phone) > 1 ) order users.name+users.email+users.phone asc limit 0,200 

which unfortunately run slow on huge database. there option optimize query?

idea query result: records has duplicates in database (for example users same name+same phone+same email

i tried inner join seems not work correctly

if want users same name, phone, , email, use group by:

select u.name, u.phone, u.email, group_concat(u.user_id) users u group u.name, u.phone, u.email having count(*) > 1; 

if want rows, rather ids in list, use join:

select u.* (select u.name, u.phone, u.email       users u       group u.name, u.phone, u.email       having count(*) > 1      ) udup join      users u      on u.name = udup.name , u.phone = udup.phone , u.email = udup.email order u.name, u.phone, u.email; 

note: these queries not original query does. instead, based on logic describe in text ("for example users same name+same phone+same email").