TEXT searching in MySQL

[charlesonthego]

I need to search in a 'text' field (the field is a product description, it has around 4000 characters).

1. What is the best way to do this without using Full Text Search features? I have one or two keywords to search for in the 'description' field only. I have around 2000 records in the table, each record has around 4000 characters in the 'description' field. I simply need to find all the records with a match.

2. If I am going to use FUll Text Search features, what is the syntax for creating the full text search index on the existing database so that the full text search commands will work?
Charles

 

[bastienk]

select * from table where textfield LIKE '%".$somevar1."%' [and | or] textfield LIKE '%".$somevar2."%'
[addtional where clauses]
[group by clause]
[order by clause]


Bastien

Cat, the other other white meat

 

[IronBeaver]

To answer your 2nd question:

CREATE FULLTEXT INDEX index_name ON table_name (col_name[(length)],... )

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax. (On BLOB and TEXT columns the length is required.)

MySQL Reference Manual (C) 2002 MySQL AB

I would suggest using FULLTEXT, its has lots of great functionality. You do take a slight performance hit when adding new records to your table however.

 

[charlesonthego]

Guys,
Thanks for coming to the rescue. I am not clear on the length specification for the creation of the FULL TEXT SEARCH index. In a description of 2000 words, if I specify a length of 100, then is it correct that only the first 100 words are used in the index and will therefore be searched. If I want the FULL TEXT SEARCH to examine all words in each description when it looks for a match, and the length of the description fields vary from say 300 words to 2000 words, what should I set the index length to and why?

BTW, the performance hit during inserts in not important, as we are searching a product catalogue, the contents of which only change when new products are added, and the insert process can be time consuming if need be in order to get improved search performance. Records are never added to this table during customer interactions.
Thanks,
Charles

 

[IronBeaver]

You asked:
"In a description of 2000 words, if I specify a length of 100, then is it correct that only the first 100 words are used in the index and will therefore be searched."

That is correct, well, lets say its the first 100 CHARACTERS of your 2000 CHARACTER length field

You asked:
"If I want the FULL TEXT SEARCH to examine all words in each description when it looks for a match, and the length of the description fields vary from say 300 words to 2000 words, what should I set the index length to and why?"

If you want the entire contents of your description searched (which you probably do), then specify the maximum length (2000) of the field in the length parameter. The length parameter is simply used to specify the first part of a string field if one choses to.