Re: Out of memory for query

[Jay Pipes]

a) The LIMIT clause is meaningless in an EXISTS subquery. EXISTS returns true as soon as one row is found.

b) You could use a join:

SELECT COUNT(*) FROM tbl1 f1
INNER JOIN (
SELECT col1, col2, MAX(col3) AS col3
FROM tbl1
GROUP BY col1, col2
) AS f2
ON f1.col1 = f2.col1
AND f1.col2 = f2.col2
AND f1.col3 = f2.col3;

If you're going to be doing this repeatedly on a table with 8M rows, I'd recommend first making an index on (col1, col2, col3)

Cheers,

Jay

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com