From: Dan Nelson
Date: April 20 2005 5:13am
Subject: Re: Unique items from all columns, very slow
List-Archive: http://lists.mysql.com/mysql/182877
Message-Id: <20050420051314.GB29613@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Apr 20), Willie Gnarlson said:
> I have been attempting to uncover a fast method for retrieving unique
> items from every column in a table. The table has approximately 60
> thousand rows. Each row has an index associated with it, and running
> 'explain select distinct class from dlist' shows that it is using the
> index.
>
> However, since there are 24 columns, running that select query for
> each column takes about 4 long seconds on a P4 2.4 Ghz.

If there are a significant number of identical values (ages, sizes,
etc), then setting PACK_KEYS=1 on the table may help by making smaller
indexes.  You might also be able to convert some columns to ENUMs,
which will make their indexes even smaller (and let your SELECT be
almost instantaneous if mysql knows how to optimize it right :)

Another alternative would be to build a table for each column, holding
the output of SELECT DISTINCT(column) FROM dlist.  Rebuild it as
frequently as needed.

-- 
	Dan Nelson
	dnelson@stripped