Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
)

enter image description here

share|improve this question

migrated from stackoverflow.com Jul 30 '13 at 16:51

This question came from our site for professional and enthusiast programmers.

1  
What indices do you have –  Mark Jul 30 '13 at 14:53

2 Answers 2

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"
share|improve this answer

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'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.