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 > 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 mobile
and people_id
columns.
Wondering if there's a faster way that returns exactly what I need.
Best Answer
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 theON
you can test for one "people_id" being less than another -- this implies that there are two rows.