hi guys, we want to generate a mysql view table to use it for Full text search here's what we want to do : select title & description from a table (mods) also use a field as a concatenated list of "tags" (table mod_tags) so if tag rows for mod_id = 62 are: bird firetruck hamburger we should find those three rows in db (select tag from mod_tags where mod_id = 62).... and concatenate them into: "bird firetruck hamburger" after that add this concatenated string as a field to the view table. then also use F(rating, num_views, date) to determine sorting where F is a function we define e.g., F(rating, views, date) = A*(views * rating^2) + B*(today - date) where A, B are constants views ;ratings ,date are selected from another table (mods_meta) this function F should be added as field in view so 4 fields for view: title, description, CONCAT(tags), F(rating, num_views, date)
i would appreciate if someone suggest me how to construct this sql view ? we prefer as much performance & efficiency building this view.
we prefer as much performance & efficiency building this view.
Relational databases have never been very good a free text searching. Have you considered using something like Lucene to provide the search indices? In my experience, Lucence has far richer search support and performs considerably better than a database backed complex search solution.