Re: Unique items from all columns, very slow

[Willie Gnarlson]

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