Seemingly simple query...excruciating response time I am in the process of running what seems to be a simple query and have yet to receive a result set. The purpose of the query is to obtain a list of all records within a table that share the same PostalCode. The table has roughly 75K rows. The PostalCode column is an indexed varchar (I had considered making this table an integer type but wanted to keep our options open for countries that use letters within their postalcodes). The query has been running about 10 minutes now and has yet to return a result. Have I done something horribly wrong???

SELECT * FROM t502locations AS t502
JOIN(

    SELECT PostalCode FROM t502locations
    GROUP BY PostalCode
    HAVING(COUNT(PostalCode) &gt; 1)

) AS vt502

ON t502.PostalCode = vt502.PostalCode
I would use LEFT JOIN with vt502 on the left side

SELECT t502.* FROM
(
    SELECT PostalCode FROM t502locations
    GROUP BY PostalCode
    HAVING COUNT(PostalCode) &gt; 1
) vt502 LEFT JOIN t502locations t502
ON vt502.PostalCode = t502.PostalCode;


or

SELECT t502.* FROM
(
    SELECT PostalCode FROM t502locations
    GROUP BY PostalCode
    HAVING COUNT(PostalCode) &gt; 1
) vt502 LEFT JOIN t502locations t502
USING (PostalCode);


Chances are, the number of PostalCodes with multiple records is probably so high that the MySQL (MariaDB) Query Optimizer does not like.

At the very least, I would run OPTIMIZE TABLE t502locations; and try these queries.