On 4/21/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > 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). > >
(...) > > 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). Right, yes. (...) > 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. It actually seems slower. The separate tables from a previous try look like this: CREATE TABLE `ET` ( `ET` double NOT NULL default '0', PRIMARY KEY (`ET`), ) ENGINE=MyISAM DEFAULT CHARSET=latin1; That table has 4781 records, but some have more (one has ~18 thousand). There are 42 "column" tables. Querying all of them took 5.58 seconds. -- Willie Gnarlson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
