Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/21/2005 01:39:15 PM:
> 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 I am SO confused!!! (and that's hard to do) You have a lookup list with eighteen thousand entries in it? Your users must pick 1 of 4781 floating point numbers from a select box? What kind of information are you trying to work with? I cannot imagine an application that uses data like you are describing. Can you help a poor fellow out and let us in on what you are working on? Here's a question, it may have taken 5.58 seconds for the first pass through all 42 tables but how fast was the second pass? Was that 5.58 measuring just the queries or the time it took to build the select boxes, too? As far as performance goes, It may be faster to check the user's entries during the form validation/processing phase than it will be to force them to enter the information the correct way the first time. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
