Have a MySQL 8 database with the following tables.
id | mobile | name
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 > 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 > 1;
I have indexes on the
Wondering if there's a faster way that returns exactly what I need.