How to optimise this Mysql query

Question

How do I optimise this query . Its taking long time. The inner query is fine. But outer query is taking more time . Please guide.Have attached the explain output.

SELECT users.ID, IFNULL( CONCAT( users.firstname, ' ', users.surname ) , '' ) AS Name, email
FROM users
where users.enabled= "Y" and users.ID in (
SELECT userID
FROM userData
WHERE paramName = 'Skills' AND paramValue IS NOT NULL AND paramValue != ''
UNION 
SELECT users.ID 
FROM users
INNER JOIN contributors ON contributors.name = users.email
)

Answer 1

MySQL is slow with subqueries except when they are in the FROM clause, so you move your subquery to a JOIN like this:

SELECT users.ID, IFNULL( CONCAT( users.firstname, ' ', users.surname ) , '' ) AS Name, email
FROM users
JOIN (
    SELECT userID 
    FROM userData
    WHERE paramName = 'Skills' AND paramValue IS NOT NULL AND paramValue != ''
    UNION 
    SELECT users.ID as userID
    FROM users
    INNER JOIN contributors ON contributors.name = users.email
) t ON t.userID = users.ID
WHERE users.enabled= "Y"

Answer 2

Here's one option:

SELECT users.ID
     , IFNULL( CONCAT( users.firstname, ' ', users.surname ) , '' ) As Name
     , users.email
FROM   users
 INNER
  JOIN (
        SELECT userID
        FROM   userData
        WHERE  paramName = 'Skills'
        AND    paramValue > ''
        UNION 
        SELECT users.ID 
        FROM   users
         INNER
          JOIN contributors
            ON contributors.name = users.email
       ) As x
    ON x.userID = users.usedID
WHERE  users.enabled = 'Y'