column weighting in FULLTEXT searches (MySQL)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

column weighting in FULLTEXT searches (MySQL)

Post by panic! »

Hello all, this is my first post!

a quick question: does anyone know how I can make one column's contents more 'important' than another's when using FULLTEXT searches?

Code: Select all

$search="naked ladies";

    $search_rs=mysql_query("select *, 

    match (`event_title`,`event_description`)  against ('$search') AS score

    from `li_events` e LEFT JOIN `li_cats` c on e.cat_id=c.idc

    WHERE MATCH (`event_title`,`event_description`)  against ('$search') 

    ORDER BY score DESC");


of the two columns searched event_title and event_description, i want event_title to be twice as important, maybe more..

Thanks in advance for any answers.

-Robin!
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Yo,

i could be wrong so if i am please be sure to correct me but...one column more important than the next? i dont think you can achieve that...the relevance of importance with full text search is more the "match" that "wear you got the match". You probably might want to make a full text search out of each individual column to isolate the search and probably do a ordery by on the column score (i dunno if that will work)

Again...im not sure of this in the first place...its just my theory
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Fulltext searching uses an fulltext index. Columns are moot once their data is put into an index. I've never tried this, but when you add your fulltext index, try adding the title field to it twice, and the description field to it once.

Failing that, you might have to do what ~kendall said & have multiple fulltext indices.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

Post by panic! »

pickle wrote:Fulltext searching uses an fulltext index. Columns are moot once their data is put into an index. I've never tried this, but when you add your fulltext index, try adding the title field to it twice, and the description field to it once.

Failing that, you might have to do what ~kendall said & have multiple fulltext indices.
I was actually thinking of having two title columns, obviously more of a hack than anything! your way seems a far more sensible way of doing it, I shall try it tomorrow at work, thank you very much!

-Robin
Post Reply