Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005 01:56:03 PM:
> On 4/20/05, Dan Nelson <[EMAIL PROTECTED]> 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.
