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

> On 4/21/05, SGreen@stripped <SGreen@stripped> wrote:
> > 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).
> >  > 
> 
> (...)
> 
> > 
> > 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). 
> 
> Right, yes.
> 
> (...)
> > 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. 
> 
> It actually seems slower. The separate tables from a previous try look
> like this:
> 
> CREATE TABLE `ET` (
>   `ET` double NOT NULL default '0',
>   PRIMARY KEY  (`ET`),
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> 
> That table has 4781 records, but some have more (one has ~18
> thousand). There are 42 "column" tables.
> 
> Querying all of them took 5.58 seconds.
> 
> -- 
> Willie Gnarlson

I am SO confused!!! (and that's hard to do) You have a lookup list with 
eighteen thousand entries in it? Your users must pick 1 of 4781 floating 
point numbers from a select box? What kind of information are you trying 
to work with? I cannot imagine an application that uses data like you are 
describing. Can you help a poor fellow out and let us in on what you are 
working on?

Here's a question, it may have taken 5.58 seconds for the first pass 
through all 42 tables but how fast was the second pass? Was that 5.58 
measuring just the queries or the time it took to build the select boxes, 
too? As far as performance goes, It may be faster to check the user's 
entries during the form validation/processing phase than it will be to 
force them to enter the information the correct way the first time. 

Thanks!

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