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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.