Dear all,

I have quite a complex problem to deal with:

My database uses a lot of InnoDB related tables. I have a database with objects that need to be searched for a) full text matches b) relational matches.

At the moment I have a sphinx instance indexing the title and description of these objects. They also have a lot of HABTM relationships with other objects and these relationships need to be part of the search filter.

I thought of having a separate "keywords" table which would have a cached counter in it to see the most common keywords (which is data I would like to have). Sort of like a tagging system.

I was wondering what the best way of doing this is.