Strategy to structure a search index in a relational database

Question

I am interested in suggestions for building an efficient and robust structure for indexing products in a new database I am building (i'm using MySql)

When a product is entered through the form there are three parts I am interested in indexing for searching purposes.

1. The product title
2. The product description
3. Tags

The most important is title, followed by tags, followed by the description.

I was thinking of using the following structure

CREATE TABLE `searchindex` (
`id` INT NOT NULL ,
`word` VARCHAR( 255 ) NOT NULL ,
`weighting` INT NOT NULL ,
`product_id` INT NOT NULL ,
PRIMARY KEY (  `id` )
)

Then each time a product is created I would split apart the title, description and tags (removing common words) and award them a weighting.

Then it is trivial to select out the words and corresponding products and order them by weighting.

Is there a better way to do this? I would be worried that this strategy would slow down over time and as the database filled up.

Answer 1

For title you might just index the first couple of characters to get a smaller subset when matching, but for the description you should really consider a fulltext index. This can automatically weight and find the best matches