MySQL Most efficient way to get all rows with at least one associated row

Question

Given a table members and a table devices, where each member can have 0-many devices, what would be the fastest way to get all members that has at least one device?

select m.*, md.* from members m
left join (
    SELECT count(*) as c, memberId from member_devices d GROUP BY d.memberId
) md ON m.memberId = md.memberId
WHERE md.c > 0

This works, but it seems really slow.

select s.*  from members m where
EXISTS (
    SELECT 1 FROM member_devices md WHERE m.memberId = md.memberId
)

Also works, and might be a little faster (?)

Any one out there with any experience? Thanks!

Answer 1

The second option, that uses EXISTS with a correlated subquery, is surely the fastest option here.

Unlike the other option it does not require aggregation and joining. Aggregation is an expensive operation, that usually does not scale well (when the number of records to process increases, the performance tends to dramatically drop).

Also, you don't actually need to count how many records there are in each group. You just want to know if at least one record is available. That's exactly what EXISTS is here for.

For performance in your query, make sure that you have the following indexes (they are probably already there if you properly implemented the relationship with a foreign key):

members(memberId)
member_devices(memberId)