Re: Optimize Database

[mike@minivend.com]

******    message to minivend-users from mike@minivend.com     ******

Quoting Brian T. Allen (brian@purenetfx.com):
> Hello All,
>     I have recently added a large database to one of my sites. It has 2
>     tables, one for basic product information and the other for very
>     specific, in-depth product information.  The first table the products.asc
>     file is 7 MB, but the second table is over 100 MB.  Does anyone have any
>     suggestions on ways to optimize this, speed up searches etc.

(None of what I am going to show applies to SQL databases.)

Only what I put in the documentation. For a file that large, you need
to use Glimpse or the binary search. Binary search works very well
for categories. It can be as simple as adding in catalog.cfg:

    Database products INDEX category

(Assuming your category field *is* called category.)

Make sure you "touch" your database to get it to build the index.

Then in your searches:

    [page scan
            dl=Video
            di=category
            ra=yes
            tf=title
            ml=10
    ]Video</A>

This will return items from even a very large database in no time at
all. Search time measured on a Pentium 200 and a 700,000 item database:
less than 200 milliseconds. (That is search time, not page build time.)

Glimpse is the best method for a full-text search; I would not have
a DB that large without using it.

>     
>     Secondly, what is the best way to access the information in the second
>     database?  What I would like is to have a button for more info on the
>     flypage, and have only information from the products.asc on the flypage,
>     and all the extended information on the second page.
> 
>     Last, I have never had success implementing the mv_max_matches directive
>     for a search.  I have it set to 250, because the database has 75,000
>     products and it takes perl too long to generate an html response to that.
>     Nonetheless, if I search for something, it will return more than 250
>     results.

Max matches is not what you want; it is ml/mv_matchlimit. If you use
a fast search method, the searching takes no time at all. It is the
HTML building and DB access that takes time.

-- 
Mike Heins                          http://www.minivend.com/  ___ 
                                    Internet Robotics        |_ _|____
Fast, reliable, cheap.              131 Willow Lane, Floor 2  | ||  _ \
Pick two and we'll talk.            Oxford, OH  45056         | || |_) |
 -- unknown                         <mikeh@minivend.com>     |___|  _ <
                                    513.523.7621 FAX 7501        |_| \_\
-
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