Fulltextindex, order by score and weight the records with phone number more.

[Olavxxx]

Hi,

I have already made a fulltextindex and it works pretty well with score and distance (calculating distance from the user and the record). However I would further like to improve this search by giving first the records with phone numbers and the ones that are "reserved".

I tried adding more columns to the order by, this does not seem to work as I had hoped it would.
(No difference in the first records).

I think there are two ways (probobly many more) to solve this:
1. Use some kind of case() to check the column reserved and order by this ( I doubt this will work better than order by the column reserved and phone )
2. Make some if/else code and making a variable that I set to 0.5 if reserved, 0.4 if has no phone no, 1 if has phone no and is reserved. Then adding the value of this variable to the score (score on the match()).

Hope this makes any sense

Olav Alexander Mjelde

 

[Olavxxx]

Hi, I thought of another idea now.. That might work better (if it works as I think it will).
I'll try to add a new colum that I call weight, with standard value of 1.

After doing that, all the records will have weight 1 and I can multiply the weight colum to the score (in the query). So if a result had 0.6 score, it still gets 0.6.
To add some weight to the score, I would need to run two queries:

update ... set weight = '1.2' where reserved is not null;
update ... set weight = '1.1' where reserved is null and phone is not null;

Then I can do order by distance desc, and score*weight

This means that the reserved ones are weighted 20% more (dynamically) and the ones with phone - not reserved, weighted 10% more. I believe this would be an ok sollution, if it works I have to wait untill after normal operating hours, as I have to update 1.4 million records and I dont want to make the website hang.

Olav Alexander Mjelde