Mike Wexler wrote:

>>>>
That may be what you want. But I want a search engine. For my database.
I want 
to be able to do search of the million records of inventory in our
database and 
find the items with certain keywords, that are currently available for
sale, in 
the venue the user is shopping in and within the price range the user
specifies. 
This is a combination of database and fulltext search that I think is
exactly in 
MySQL's marketplace.
<<<<

Personally I use the term 'search engine' to mean Google-like text-only
search. I don't have a term for the above, but FTS seems fine. I agree
that MySQL users will tend to want an index over several columns
including one that is text (FTS).

As for practical advice now, I'll tell you what we did:

1. Got better hardware. Athlons, DDR memory, RAID: more is better. :)

2. We split the FTS tables into 10 tables. Since we do not need to
search the text without some other constraint (yet -- I'd like FTS to
work with merge tables in the few cases where we did), we know which
table to use. Your mileage may vary. This helps for several reasons:
less index to pull into memory for the search, and FTS does not seem to
be O(n) but rather O(n^2) though I can't be sure.

3. Replicated the table to do FTS on to another computer. All it does is
FTS. We made our own routine to replicate the table (actually 10 tables)
since our text is compressed on the main server (smaller = less disk
access = faster) and the indexes are different (obviously). In this
case, it can hold a lot of the index in memory -- big difference. Also,
a long search does not lock the tables. This is worth in and of itself.
Even if you have this copy of the table on the same server.
De-normalization can produce huge speed increases depending on what and
how you do things.

4. If you do #2 and #3 you'll notice that you can have x (10 for us)
number of servers partition the FTS. We don't actually do this, but we
could and therefore get 'Distributed Fulltext' -- the title of this
thread!!!

Number 1 and 3 should work for everyone, I think. Only if your app can
partition, can you do #2 and #4. 

Sincerely,
Steven Roussey
http://Network54.com/?pp=e 




---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to