Searching a complicated mysql database
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.