通过搜索令牌问题的MYSQL自定义查询顺序

Question

Right now i'm building a custom query using php and mySql. Say I've a string Hello this is me. For this

select distinct(user_primary.id) as id 
from user_primary, 
user_sec, 
user_location, 
user_ind_cat 
where 
( 
 user_primary.status != 3 
 and 
 (
  user_primary.id = user_sec.job_id
 ) 
 and 
 (
  user_primary.id = user_ind_cat.job_id
 )
) 
and 
( 
 (
  user_primary.name like "%Hello%"
 ) 
 or 
 (
  user_primary.name like "%this%"
 ) 
 or 
 (
  user_primary.name like "%is%"
 ) 
 or 
 (
  user_primary.name like "%me%"
 ) 
 and 
 (
  user_primary.name like "%Hello this is me%"
 )
) 
and 
(
 user_primary.login <= 1415426357
) 
limit 0, 150

So far it was working fine until recently I've found a issue. Whenever I ran this kind of query it generates results contains user's with full name like the search token and other matching tokens generated from search token. But the actual row which has perfect match with provided token "Hello this is me" is not showing in top.

Let me explain the results if i ran current query,

1. "Hello how are you"
2. "this is nixon"
3. "Hello this is me"
4. "oh hello there"
5. "I'm me"
6. "hello you there"

I want to show the actual result in the top, so the results will be look like this,

1. "Hello this is me"
2. "Hello how are you"
3. "this is nixon"
4. "oh hello there"
5. "I'm me"
6. "hello you there"

Can anyone please tell me what's the problem here? Or should I remove or add the query?

Thanks in advance,

Nixon

Answer 1

This sounds like a job for a FULL TEXT INDEX!

 ALTER TABLE `user_primary` ADD FULLTEXT INDEX (`name`);

MySQL has now created a fuzzier search index for text than your chain of OR's. So, part of your query will look like:

 SELECT name, MATCH(name) AGAINST ('Hello this is me') as confidence 
     FROM user_primary
     WHERE MATCH(name) AGAINST ('Hello this is me')
     ORDER BY confidence DESC

The better the match, the higher confidence will be, so "Hello this is me" should be on top.

This might be your query, cleaned up and untested:

select distinct(user_primary.id) as id , MATCH(name) AGAINST ('Hello this is me') as confidence
from user_primary, user_sec, user_location, user_ind_cat 
WHERE 
 user_primary.status != 3 
 and user_primary.id = user_sec.job_id 
 and user_primary.id = user_ind_cat.job_id
 and MATCH(name) AGAINST ('Hello this is me')
 and  user_primary.login <= 1415426357
ORDER BY confidence DESC
limit 0, 150