Optimize MariaDB query with table that self-joins
What the heck is the intent?  It seems to deliver the ids of all pairs of individuals with the same first_name.

It looks like a "group-wise" max, but not quite.

weight seems to be irrelevant since it is the constant "5".

INDEX(contact_type, first_name, id)


may speed it up, even with 99.9% of contact_types being the desired value.  The speedup is due to the index being "covering".  The order of the columns is important.

It is likely to return a million rows.  That, in itself, will take time.  And possibly choke the client.

Just looking for dups?

Perhaps you want

SELECT GROUP_CONCAT(id), first_name
    FROM t
    WHERE first_name != ''
      AND contact_type = 'Individual'
    GROUP BY first_name
    HAVING COUNT(*) &gt; 1;


Alternatively, SELECT MIN(id), MAX(id), first_name ..., except that it does not give you all the ids when there are more than 2.

Optimal:

INDEX(contact_type, first_name, id)