MySQL Search (Sort by Relevance)

Question

Hi guys,

Can any one help me how to sort rows by relevance for the following criterion ?

`tbluser`
- - - - - - -
First Name
Last Name

`tbleduc`
- - - - - - -
School
College
University

On the search form the user has following fields

Name
School
College
University

Where School College and University are Optional..

And Name is split into 2 words (other words in middle are omitted), first word is taken as first anme and last word as last name..

Now I would like to implement search based on relevance.

Thanks for the help :)

Answer 1

Step 1: Define a method of calculating "relevance".

Step 2: Write a query which uses the calculation from step 1 to determine the order of its results.

Unfortunately, you haven't stated what makes one record "more relevant" or "less relevant" than another, so that's about as much as we can tell you at this point.

Answer 2

Okay, I tried this for you on Postgres (I don't have MySQL here, so maybe it's a little bit different):

select matches.id,
      (matches.tfirstname
       + matches.tlastname 
       + matches.tschool
       + matches.tcollege
       + matches.tuniversity) as total
from (
   select u.id,
          (case when u.firstname like '%a%' then 1 else 0 end) as tfirstname,
          (case when u.lastname like '%b%' then 1 else 0 end) as tlastname,
          sum(e2.nschool) as tschool,
          sum(e2.ncollege) as tcollege,
          sum(e2.nuniversity) as tuniversity
   from tbluser u left outer join (
      select e.usr,
             (case when e.school like '%c%' then 1 else 0 end) as nschool,
             (case when e.college like '%d%' then 1 else 0 end) as ncollege,
             (case when e.university like '%e%' then 1 else 0 end) as nuniversity
      from tbleduc e
   ) e2 on u.id=e2.usr
   group by u.id, u.firstname, u.lastname
) as matches

I used these DDL statements to create the tables:

create table tbluser (
  id int primary key,
  firstname varchar(255),
  lastname varchar(255)
)


create table tbleduc (
  id int primary key,
  usr int references tbluser,
  school varchar(255),
  college varchar(255),
  university varchar(255)
)

And a little bit of example data:

insert into tbluser(id, firstname, lastname)
            values (1, 'Jason', 'Bourne');
insert into tbleduc(id, usr, school, college, university)
            values (1, 1, 'SomeSchool', 'SomeCollege', 'SomeUniversity');
insert into tbleduc(id, usr, school, college, university)
            values (2, 1, 'MoreSchool', 'MoreCollege', 'MoreUniversity');

The query can be simplified a bit, if the relationship between tbluser and tbleduc is 1:1.

Don't forget to replace the %a%, %b, ... with your variables (I recommend using a prepared statement).

I hope this template helps as a basic solution - you can tweak it as much as you like :-) You can also remove the outermost select statement, to get the counters of the individual results.

Answer 3

Do you have the time and resources to try to implement Solr? It is much better for relevancy-ranked searching, and is surprisingly easy to get started with.

Answer 4

Have you tried the Match - Against query on your table. The result set is sorted by relevance y default. Plus it does Full text search.