Strategy to structure a search index in a relational database
- by neilc
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.
The product title
The product description
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.
