Re: simple searches on large data sets

[Kyle Hayes <kyle540@quicknet.net>]

Have you tried putting the products table into an SQL database?  250k rows
is nothing to a reasonable DB engine.  Adding an index is a trivial 20
second task with MySQL.  With Oracle (at a different company in a
different life), I regularly got sub-second response times out of a table
with 250 _million_ rows of data.  Of course, it was running on a large
system.  A factor of one thousand in size will make up for any different
in hardware.

If you really have that many product rows, I would strongly recommend
using a SQL database.  The docs show how to do this.  It is relatively
painless.  In fact, MV will even create the table and populate it for you
from the .asc file if you do it correctly.

Best,
Kyle  

On Wed, 30 Jun 1999, Robert Fournerat wrote:

> ******    message to minivend-users from Robert Fournerat <robert@netin.com>     ******
> 
> Thanks Bill!
> 
> Using the method that uses binary searches, I was able
> to drop the search time to around the 1 second mark.
> 
> But I had to build the products.asc.code file by hand
> (well, I mean with the normal unix sort command).  Is
> there a way to have this file built automatically?  I
> thought the 
> 
> Database            products     INDEX             code
> 
> entry in the catalog.cfg file would do that, but I 
> guess not.  Maybe I should just sort the products.asc
> file (on the code field) before putting it in place
> (and not use the mv_search_file line)???
> 
> thanks,
> Robert
> 
> 
> Bill Betz wrote:
> > 
> > ******    message to minivend-users from "Bill Betz" <bill@pennherb.com>     ******
> > 
> > ----- Original Message -----
> > From: Robert Fournerat <robert@netin.com>
> > To: minivend-users <minivend-users@minivend.com>
> > Sent: Tuesday, June 29, 1999 9:56 PM
> > Subject: simple searches on large data sets
> > 
> > > ******    message to minivend-users from Robert Fournerat
> > <robert@netin.com>     ******
> > >
> > > Hi,
> > >
> > > I have a products.asc with about 250,000 items (but only
> > > 4 columns).  The way I have things configured right now,
> > > it takes mv (3.14) about 16 seconds to return an item from
> > > a search (ie: search for a part number in the code column).
> > > When I grep for the part number, grep returns in less than
> > > a second.
> > >
> > > How can I optimize this search, to return the page more
> > > quickly?
> > >
> > > I am using the search facility in "leftside", and have
> > > added:
> > >
> > > [set hardpart_profile]
> > >   mv_search_field=0
> > >   mv_orsearch=no
> > >   mv_substring_match=no
> > >   mv_coordinate=yes
> > >   mv_exact_match
> > > [/set]
> > >
> > > in leftside as well.  I have defined
> > >
> > > Database            products     products.asc      TAB
> > > Database            products     INDEX             code
> > >
> > > in the catalog.cfg file.
> > >
> > > Is there anything else I can do?
> > 
> > Try Glimpse - it cut my search times in half.
> > 
> > 1. Turn on Glimpse in catalog.cfg.
> > 2. Run glimpseindex on products.asc (glimpseindex -b -H
> > /path/to/your/productsdir/products products.asc)
> > 3. Put this in your search specs -- <INPUT TYPE=HIDDEN NAME=mv_searchtype
> > VALUE="glimpse">
> > 4. Instant speed increase.
> > 
> > Try fast binary search on your products.asc.code file. This gives fast
> > results for (in your case) "code" field.
> > 
> > <INPUT TYPE=TEXT NAME="mv_dict_look" SIZE=6>
> > <INPUT TYPE=hidden NAME=mv_dict_limit    VALUE="-1">
> > <INPUT TYPE=HIDDEN NAME=mv_search_file VALUE="products.asc.code">
> > <INPUT TYPE=HIDDEN NAME=mv_return_fields VALUE="1">
> > <INPUT TYPE=HIDDEN NAME=mv_substring_match VALUE="yes">
> > <INPUT TYPE=HIDDEN NAME=mv_dict_fold VALUE="yes">
> > <INPUT TYPE=HIDDEN NAME=mv_matchlimit VALUE="10">
> > 
> > Regards,
> > 
> > Bill Betz
> > bill@pennherb.com
> > Penn Herb Company, Ltd.
> > --
> > Visit our websites at:
> > http://www.pennherb.com and http://www.olbas.com
> > --
> > Over 7000 herbs and healthy products on line at:
> > http://www.pennherb.com/cgibin/herbstore.cgi
> > --
> > 
> > -
> > To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
> > email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
> > Archive of past messages: http://www.minivend.com/minivend/minivend-list
> -
> To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
> email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
> Archive of past messages: http://www.minivend.com/minivend/minivend-list
>