List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 20 2005 6:59pm
Subject:Re: Unique items from all columns, very slow
View as plain text  
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 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
> 
> There are, but PACK_KEYS=1 seemed to shed about a second (just
> guessing, but it did seem only slightly faster) off the total query
> time.
> 
> > 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 :)
> 
> One column was already ENUM, but it wouldn't be possible to make any
> others that type.
> 
> > 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.
> 
> Okay, I decided to try that option, as it sounded like a good one. I
> created the tables, and since the main table would be rarely updated I
> was going to run DELETE FROM on the column tables and run the queries
> `INSERT INTO table (col) SELECT DISTINCT col FROM dlist' each time a
> new record was updated or added.
> 
> It turns out that is almost as slow as the original method. So then I
> added an index to every column table, and it was still no better.
> 
> I thought maybe generating a select box for each column's contents was
> the problem, since unique items in some tables are as much as 4
> thousand, but singling out the code which performs the queries,
> eliminating the select box generation, confirmed that the queries were
> indeed the slow part.
> 
> I may have to abandon the retrieving of unique items from every column
> in a table unless you or anyone else have any other ideas.
> 
> Thanks for your help, Dan.
> 
> -- 
> Willie Gnarlson
> 

If you are using select boxes for every column, how can you possibly worry 
about new or changed values as the users must pick from a fixed list of 
values, don't they?

You don't rebuild _all_ of your umpteen pick-list tables from scratch 
every time, detect just the new or changed values and add/update them 
(which goes back to my original question of how are the users creating new 
values if you are really using a select box and not something else like a 
combo box (part list, part text field))

Maybe if I understood your front-end application a little bit better, I 
could help you work around the issue. The way you described your user 
interface design made me wince; something just doesn't feel right about 
it.


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