On Mar 19, 5:11 pm, Chris <spam_me_...@goaway.com> wrote:
> How do I count the unique values in a column in a very large table?
>
> I could do this:
>
> select mycolumn
> from mytable
> group by mycolumn;
>
> and wait to see how many rows come back. The trouble is that the table
> has 75 million rows, and the MySQL Query Browser crashes with an
> out-of-memory error after 5 million rows have come back.
>
> All is need is a single number: the count. There's got to be a way to do
> it in SQL alone.

I think 'values that only appear once' can be found like this:

SELECT t1.mycolumn, t2.mycolumn
FROM mytable t1
LEFT JOIN mytable t2 ON t2.word_id <> t1.word_id
AND t2.mycolumn = t1.mycolumn
WHERE isnull( t2.mycolumn )