• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

construct complex table view

 
Yahya Elyasse
Ranch Hand
Posts: 510
Eclipse IDE Google Web Toolkit Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

thanks for your interest.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Yahya Elyasse
Ranch Hand
Posts: 510
Eclipse IDE Google Web Toolkit Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your reply,

we are considering to use www.sphinxsearch.com

we need to first build the view than feed it to sphinx config file .

i would appreciate if you help constructing the view as i described above.

thanks
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic