Optimized MySQL query with complex calculation
Think one problem is that you have no key that it is using on the provider table, hence it is checking every row.

Not tested the following but this might be faster. Uses the provider_categories table as the first one (on which you are limiting the number of rows with a keyed check) and from that joining to the provider table.

SELECT b.id,
    {$algorithhm} AS score
FROM provider_categories a
INNER JOIN provider b ON a.provider_id = b.id
LEFT OUTER JOIN users_provider c ON a.provider_id = c.provider_id AND user_id = {$current_user-&gt;id}
WHERE c.provider_id IS NULL AND a.category_id IN ( {$categories} )
ORDER BY score DESC
LIMIT 3