Re: Unique items from all columns, very slow

[Willie Gnarlson]

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