I'm looking for a good way to count how many children a person has, given that each person has a mother and father attribute. My use case is a little unique in that a person can be either a mother or a father (strange biology).
Given a table like this:
person_id father_id mother_id 123 321 324 321 324 555 456 321 324
Currently the only index is person_id and all IDs are integer types.
person_id child_count 324 3 321 2 555 1 123 0 321 0 456 0
I have one potential solution:
SELECT parent.person_id, COUNT(child.person_id) AS child_count FROM person AS parent LEFT JOIN person AS child ON parent.person_id IN (child.father_id, child.mother_id) GROUP BY parent.person_id
But it is extremely slow. I have over 1,000,000 people in my table and this query crashes when the limit is higher than about 1,000 or so. I'm looking for a way to do this using far less table scans, perhaps with windowing or a similar technique.