Using JOIN to find duplicates in another table taking too long Have a MySQL 8 database with the following tables.
people
id | mobile | name

registrations
id | people_id | other_stuff

I am trying to find the mobile number and the number of times it's duplicated by registrations. Some records in the people table do not have foreign records in the registrations table, so need to exclude them from our calculation.
If I do the following, the query runs relatively fast (500ms), but it excludes two or more registrations that have the same people_id
select mobile, count(mobile) as count from people where exists 
(select people_id from registrations where people_id = people.id) group by mobile having count &gt; 1;

The following returns what I need, it returns the above but also takes into account registrations with the same people_id, but the problem is, this takes about 6 seconds for around ~250,000 rows in patients and registrations.
select mobile, count(mobile) as count from people inner join registrations on 
registrations.people_id = people.id group by mobile having count &gt; 1;

I have indexes on the mobile and people_id columns.
Wondering if there's a faster way that returns exactly what I need.
I can't imagine that the first query would work.  The subquery needs count but that is not available until the entire query is finished.  The outer select can't move forward until the subquery provides the existence of each row.
Try to turn the entire query into a JOIN; no subquery.  In the ON you can test for one &quot;people_id&quot; being less than another -- this implies that there are two rows.