Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005 05:46:25 PM:
> On 4/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > 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 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). > <snip> > > -- > Willie Gnarlson 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). This kind of arrangement is very common within databases that support robust user interfaces. It takes WAY too long (as you already found out) to dynamically regenerate the full list of allowable values every time. What happens to your select boxes if you have an empty data table? Does that mean that the users can pick from NO values in any column? By putting each pick list in its own table, you separate UI support data from your application's "real" data. 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. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
