List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 20 2005 5:13am
Subject:Re: Unique items from all columns, very slow
View as plain text  
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
Thread
Unique items from all columns, very slowWillie Gnarlson20 Apr
  • Re: Unique items from all columns, very slowDan Nelson20 Apr
    • Re: Unique items from all columns, very slowWillie Gnarlson20 Apr
      • Re: Unique items from all columns, very slowSGreen20 Apr
        • Re: Unique items from all columns, very slowWillie Gnarlson20 Apr
          • Re: Unique items from all columns, very slowSGreen21 Apr
            • Re: Unique items from all columns, very slowWillie Gnarlson21 Apr
              • Re: Unique items from all columns, very slowWillie Gnarlson21 Apr
              • Re: Unique items from all columns, very slowSGreen21 Apr
                • Re: Unique items from all columns, very slowWillie Gnarlson21 Apr