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