Need to optimize a nested select statement

Question

I've got the following SQL:

SELECT customfieldvalue.ISSUE
FROM   customfieldvalue
WHERE  customfieldvalue.STRINGVALUE 
IN (SELECT customfieldvalue.STRINGVALUE
    FROM   customfieldvalue
    WHERE  customfieldvalue.CUSTOMFIELD = "10670"
    GROUP  BY customfieldvalue.STRINGVALUE
    HAVING COUNT(*) > 1);

The inner nested select returns 3265 rows in 1.5secs on MySQL 5.0.77 when run on its own. The customfieldvalue table contains 2286831 rows.

I want to return all values of the ISSUE column where the STRINGISSUE column value is not exclusive to that row and the CUSTOMFIELD column contains "10670".

When I try and run the query above, MySQL seems to be stuck. I've left it run for up to a minute, but I'm pretty sure the problem is my query.

Answer 1

Try something along these lines:

    SELECT cfv1.ISSUE
    COUNT(cfv2.STRINGVALUE) as indicator

    FROM   customfieldvalue cfv1

    INNER JOIN customfieldvalue cfv2
    ON cfv1.STRINGVALUE = cfv2.STRINGVALUE AND cfv2.CUSTOMFIELD = "10670"

    GROUP BY cfv1.ISSUE
    HAVING indicator > 1

This probably doesn't work on copy&paste as I haven't verified it, but in MySQL JOINs are often much much faster than subqueries, even orders of magnitude.