List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 21 2005 1:39pm
Subject:Re: Unique items from all columns, very slow
View as plain text  
Willie Gnarlson <willie.gnarlson@stripped> wrote on 04/20/2005 05:46:25 
PM:

> On 4/20/05, SGreen@stripped <SGreen@stripped> wrote:
> > Willie Gnarlson <willie.gnarlson@stripped> wrote on 04/20/2005 
01:56:03 PM:
> >  > On 4/20/05, Dan Nelson <dnelson@stripped> wrote:
> >  > > 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 
isusing 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.
> >  > > 
<snip - see previous posts on this thread for details>
> Okay, they aren't creating new values using that form. The form I've
> been talking about is a form to limit results from the database to
> only specific values for  specific fields without the user having to
> know from memory what the possible values are (plus, they'll change
> every so often).
> 
<snip>
> 
> -- 
> Willie Gnarlson

If I read that last part correctly, you have already identified the need 
for a mechanism for managing the value lists separately from what the user 
enters. In this case you should have everything in place (or almost in 
place) to put each list into its own table (Someone else suggested this 
design, too).

This kind of arrangement is very common within databases that support 
robust user interfaces. It takes WAY too long (as you already found out) 
to dynamically regenerate the full list of allowable values every time. 
What happens to your select boxes if you have an empty data table? Does 
that mean that the users can pick from NO values in any column?  By 
putting each pick list in its own table, you separate UI support data from 
your application's "real" data. 

Run your queries once to build your lists then use another form (or even 
use a separate application) to manage the lists. Because it's a one-time 
event, getting the first set of unique values can take all night if it 
needs to. What you need to be fast is the building of the select boxes. By 
having those lists ready-to-go on their own tables, it will be as fast as 
a sequential read from the disk (very fast) or a read from the disk cache 
(faster) or a read from the query cache (fastest). In any case, running 
the queries will no longer slow you down.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
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