Keep in mind that if you create an index on multiple fields, then all of those fields must be searched at once. You can't index product_name, product_desc and product_category for instance, then only search on the product_name field using MATCHES.
If you want to bypass this (and many other limitations, including stoplists, short words, etc) then I would suggest indexing your data with a seperate text indexing system like Jakarta Lucene (http://jakarta.apache.org/lucene/docs/index.html). Using a product like Lucene will also allow you to implement your parametric searching MUCH easier. You can either define additional parametric fields in you lucene index or you could create a second one and with the API very easily merge the searches between the indexes. Keep in mind that a major limitation of the mysql fulltext engine is that it can't index more than 500 characters which could be a major drawback for your parametric data. The following assumes you will stick with mysql fulltext indexes... In order to index 3 letter words, you will need to set min_ft_word_len in your mysql.cnf file. You probably also want to create your own list of stopwords and use ft_stopword_file. If your table is large then the biggest problem you are going to have when creating the index is that the table will be locked while the index is being created. If that is a problem then I would suggest that you create a seperate table with "create table as". You will need some way of keeping track of any additions to the "base" table at this point, perhaps using the highest product_id, or if you have a modification timestamp on your table use that. Create the text index on the new table and test it out. When you are satisfied that everything is working ok then rename the new table to the old table, and insert/update the records that have been added/modified since you created the copy. If you can suffer the downtime then simply create the index on the table and wait it out. There is very little danger in adding the text index to a production table. Just back up your database before you make the modifications and you should have no problems whatsoever. --- Stuart Grimshaw <[EMAIL PROTECTED]> wrote: > Hi All, > > I'm currently engaged in a project to implement > full text searching > of our product database. The current implementation > was written for an > older version of MySQL and doesn't implement BOOLEAN > MODE. > > Currently, the full text search is against a > de-normalised table from > fields icluding the products ID, title & > description, in a simple > table : > > CREATE TABLE text_search > { > product_uid int(10) NOT NULL, > rank int(10) NOT NULL DEFAULT 0, > txt TEXT > } > > with the full text index set up against txt. > > There are several problems with this implementation, > firstly the > de-normalised "txt" field doesn't include all > information on a > product, so I would like to implement the search > against the full > product table. There are approx 65,000+ products in > the table, and the > index would be on 2 or 3 fields in that table. Has > anyone retro-fitted > a full text index to a production database? Is there > an established > strategy for doing this? > > Because of the nature of our business we sell a lot > of products where > the keyword is 3 letters, DVD, USB, DDR etc etc. The > manual mentions > that while you can reduce the minimum number of > letters, it's not a > good idea "Modifying the default behavior will, in > most cases, make > the search results worse." it says. Is there a > better way to ensure > these search terms are included in the index? > > Finally, we also carry parimetric data on our > products, it would be > good to include this information in the full text > search. The only way > I can think of is to create a seperate full text > index on the table > storing the parimetric data, and then run the query > against both > tables, mergeing the results in code. > > -- > -S > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
