0

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.

2
  • 2
    Provide complete CREATE TABLE for both tables. Show tables statistic (total, matched and returned rows amount). Provide execution plan for your queries. PS. Both queries are synthactically wrong and must fail while running as-is, correct them.
    – Akina
    Mar 19 at 5:03
  • 1
    provide explain plan of both queries. Mar 20 at 3:15
0

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 "people_id" being less than another -- this implies that there are two rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.