SQL - Putting the most relevant results at the top?

AARRRGH

Hello

This is a different query to this one I posted last week.

This query relates to the sites in my signature. Basically these sites contain (most of) the jobs from a number of different jobsites.

Let's imagine someone does a search for an "accounts" job. At the moment this will search the title and body of each job advert for the word "accounts". The results are then displayed by date.

I would like to change this so they are still displayed by date, but they are then sorted by title first, and then body second. This is because job adverts with "accounts" in the title are probably more relevant than job adverts with "accounts" in the body.

Do you know what I mean?

Does anyone know how I can do this without greatly slowing down the search results?

Is this simply adding title to the order by, as in "order by date, title, body desc"?

I am using MySQL.

Thank you.

trellheim

just add the order by as you stated. Since it's a website you'll have to test to see if acceptable performance results are achieved.

AARRRGH

Yep, I'll test it tonight and see what the story is.

Although my server is fairly powerful (four dual core processors) my sites get a lot of traffic so I am sensitive about slowing things down.

Thanks for the reply.

lh

Doing an order by like that won't work, well won't give you your desired results as the word accounts may not be in the title and may just be in the body

Not 100% on MySQL but in MSSQL this is what you would need if you wanted to prioritise say a title over a body with a search card, where it could be in either....

Select
CreatedDate,
Title ,
Body,
'SortByTitle' = case when Title like '%Accounts%' then 0 else 1 end
'SortByBody' = case when Body like '%Accounts%' then 0 else 1 end
From Jobs
Where Title like '%Accounts%' or Body like '%Accounts%'
Order by CreatedDate, SortByTitle, SortByBody

amen

you may get better performance by writing two single sql statement and using a UNION instead of the OR

you should profile both ways on real data and see which is best

Eoin

Yep, I think the following "order by date, title, body desc" will purely do it alphabetically, and not attach any weighting to the search string.

Ginger

If you are using MSSQL you can use Full Text Search and do a weighted search where you can specify what fields have a higher weighting..

You can use Ranking and Free Text searching to help with it (CONTAINSTABLE keyword for example)

Cantab.

Create a tf-idf table for all your job adverts. Search is very fast once the table is created.

Use a porter stemmer to solve many of the singular/plural problems.

Much richer search techniques (semantic analysis, domain-specific language modelling, etc.) could also be applied.

Think careerjet.co.uk

AARRRGH

Lots to think about there guys, thank you.

GoneShootin

Could do 2 seperate queries. One lists records in order by date

WHERE TITLE LIKE SEARCHSTRING,

then another one

WHERE BODY LIKE SEARCHSTRING AND TITLE NOT LIKE SEARCHSTRING

This will give priority to title by date, and then body by date.

none

Since most suggestions here revolve around LIKE clause and you mentioned performance concerns, make sure you understand implications of using LIKE, e.g. http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx.

AARRRGH

lh wrote: »

Doing an order by like that won't work, well won't give you your desired results as the word accounts may not be in the title and may just be in the body

Not 100% on MySQL but in MSSQL this is what you would need if you wanted to prioritise say a title over a body with a search card, where it could be in either....

Select
CreatedDate,
Title ,
Body,
'SortByTitle' = case when Title like '%Accounts%' then 0 else 1 end
'SortByBody' = case when Body like '%Accounts%' then 0 else 1 end
From Jobs
Where Title like '%Accounts%' or Body like '%Accounts%'
Order by CreatedDate, SortByTitle, SortByBody

Thanks once again everyone for your help. I implemented a solution like the above. Basically I do my select and then I order by all the words in the title, then some of the words in the title, then all the words in the body, then everything else. There is no noticable performance difference (measuring the results I can see it is 100th of a second slower - I am ok with this.)

I have a new question now. Well, this is more of a request for your opinion.

I have an advanced search on my sites, for example: http://www.dublinjobs.ie/advanced.php?all=&exact=&some=&none=&title=&location=

I can't really use the ordering system from above, as the user is able to create fairly complex queries. For example, it would be difficult to sort the results by title then body if the user does a query which excludes some items, includes others, and looks for other bits and pieces in the title, etc. Basically creating the ordering string would be hard, and I don't even really know if sorting an advanced search by title then body is even relevant for an advanced search.

So my question to you is this: would it piss you off that your advanced search results are sorted by date rather than title then body?

Maybe it's a non-issue, but your opinions are appreciated anyway.

Thanks!

mdebets

You could do the new query fairly easy the following way:

Select
CreatedDate,
Title ,
Body,
1
From Jobs
Where
Put here all conditions having to do with the Titel

UNION
Select
CreatedDate,
Title ,
Body,
2
From Jobs
Where
Put here all conditions having to do with the Body

Order by CreatedDate, 4

That should give you everything ordered first by date, and then sorted by if you found the input in the title or the body