Advice on MySQL ful text indexing and boolean

phpdino

Hi, I have implemented relevance ranking on this database using the mysql full text indexing which uses the scoring to order results. The complaint now is from users that want their results narrowed to results that include the multiple keywords they enter. For example if they enter "help sheets" they basically want the records that have "help AND sheets" together in that order. I thought the point of relevance ranking was to have the most relevant (highest score) at the top and descending scores following that. Is there a way to provide both? The query is basically this:


SELECT * FROM (
                             SELECT page_id, 
				((MATCH (title) AGAINST ('$keywords' )) *8 + (MATCH (text) AGAINST ('$keywords' ) ) *4 ) AS score
				FROM pages 
			) A WHERE score>0

Thanks!

dagon

11.9.2. Boolean Full-Text Searches

johanafm

First off, for the sake of clarity, your query should in my opinion be

SELECT page_id,
		((MATCH (title) AGAINST ('$keywords' )) *8 + (MATCH (text) AGAINST ('$keywords' ) ) *4 ) AS score
FROM pages A
HAVING score>0

The reason is that your query seems to be utlizing a where clause, but it is in fact nothing but a disguised HAVING clause. You have an inner query with one field 'score' which is the result of an aggregate function and as such can't be used in a where clause since that data is not available until after having retrieved the results - which are the ENTIRE TABLE.
Looking briefly at your query it's not apparent that you are in fact doing the exact same thing as my query above.
On the other hand, you could stick the match against in the where clause directly instead.

Also note that you can't specify that you want records that contain both 'help' and 'sheets' in that order. You can specify that they must have both, but that would include a record with the text "Sheets for help". You can specify that you want the exact string "help sheets", by using a quoted string literal, but on the other hand that would not include "help, where are my sheets?".
Boolean searches also doesn't automatically give you a weighted result.

However, I'm wondering if it couldn't be that your "double weight to title" approach is screwing things up. It could be (check it yourself!) that the title "This article is NOT about sheets" would be ranked higher than a text entitled "Database Fundamentals" where the text states "These help sheets contain some important facts to remember when working with databases"...
Also, why multiply one field by 8 and the other by 4 when it's enough to multiply one by 2?

phpdino

johanafm;10978996 wrote:
First off, for the sake of clarity, your query should in my opinion be
SELECT page_id,
		((MATCH (title) AGAINST ('$keywords' )) *8 + (MATCH (text) AGAINST ('$keywords' ) ) *4 ) AS score
FROM pages A
HAVING score>0
The reason is that your query seems to be utlizing a where clause, but it is in fact nothing but a disguised HAVING clause. You have an inner query with one field 'score' which is the result of an aggregate function and as such can't be used in a where clause since that data is not available until after having retrieved the results - which are the ENTIRE TABLE.
Looking briefly at your query it's not apparent that you are in fact doing the exact same thing as my query above.
On the other hand, you could stick the match against in the where clause directly instead.

Also note that you can't specify that you want records that contain both 'help' and 'sheets' in that order. You can specify that they must have both, but that would include a record with the text "Sheets for help". You can specify that you want the exact string "help sheets", by using a quoted string literal, but on the other hand that would not include "help, where are my sheets?".
Boolean searches also doesn't automatically give you a weighted result.

However, I'm wondering if it couldn't be that your "double weight to title" approach is screwing things up. It could be (check it yourself!) that the title "This article is NOT about sheets" would be ranked higher than a text entitled "Database Fundamentals" where the text states "These help sheets contain some important facts to remember when working with databases"...
Also, why multiply one field by 8 and the other by 4 when it's enough to multiply one by 2?

Hi johanafm, thanks for the response. i'm trying to satisfy some users but it seems impossible to keep relevancy which works really well AND to allow them to search for say "help AND sheets" only. You are right the weights can just be 2 for title but I think there was a reason I made it 8 and *4. Of course I don't recall now :S.

phpdino

Is there some way I could give a higher rating to results with exact same words in the title?

dagon

phpdino;10979030 wrote:
Is there some way I could give a higher rating to results with exact same words in the title?

yes. here is an example from my site, its raw so you will have to interpret it

$sql_1 = "SELECT url, author, title, body,
(
MATCH (title,author)
AGAINST ('$sql_search') 
)
+
(
MATCH (body)
AGAINST ('$sql_search' ) 
)
AS relevance
FROM search
WHERE MATCH (title, body,author)
AGAINST ('$sql_search' IN BOOLEAN MODE) 
$sql_within
HAVING relevance >0
ORDER
BY relevance DESC";

/* for 50% threshhold avoidance  and fuzzy matching*/

$sql_2 = "SELECT url, author, title, body, 
 ( (1.3 * (MATCH(title,author) 
AGAINST ('$sql_search' IN BOOLEAN MODE)))
 +
 (0.6 * (MATCH(body) 
AGAINST ('$sql_search' IN BOOLEAN MODE))) ) 
AS relevance 
FROM search 
WHERE ( MATCH(title,body,author) 
AGAINST ('$sql_search' IN BOOLEAN MODE) ) 
$sql_within
HAVING relevance > 0 ORDER BY relevance DESC";

//if those fail, last ditch attempt

	$sql_3 = "
SELECT url, author, title, body, 
10*(
LENGTH(title) -
LENGTH(REPLACE(LOWER(title),LOWER('$sql_search'),'')))/LENGTH('$sql_search') 
+ 
10*(
LENGTH(author) -
LENGTH(REPLACE(LOWER(author),LOWER('$sql_search'),'')))/LENGTH('$sql_search') 
+ 
0.1*(
LENGTH(body) -
LENGTH(REPLACE(LOWER(body),LOWER('$sql_search'),'')))/LENGTH('$sql_search')
as relevance
FROM search
WHERE
( title REGEXP ' $sql_search ' OR body REGEXP ' $sql_search ' )
$sql_within
HAVING relevance>0
ORDER BY relevance DESC 
";

phpdino

dagon;10979040 wrote:

yes. here is an example from my site, its raw so you will have to interpret it

$sql_1 = "SELECT url, author, title, body,
(
MATCH (title,author)
AGAINST ('$sql_search') 
)
+
(
MATCH (body)
AGAINST ('$sql_search' ) 
)
AS relevance
FROM search
WHERE MATCH (title, body,author)
AGAINST ('$sql_search' IN BOOLEAN MODE) 
$sql_within
HAVING relevance >0
ORDER
BY relevance DESC";

/* for 50% threshhold avoidance  and fuzzy matching*/

$sql_2 = "SELECT url, author, title, body, 
 ( (1.3 * (MATCH(title,author) 
AGAINST ('$sql_search' IN BOOLEAN MODE)))
 +
 (0.6 * (MATCH(body) 
AGAINST ('$sql_search' IN BOOLEAN MODE))) ) 
AS relevance 
FROM search 
WHERE ( MATCH(title,body,author) 
AGAINST ('$sql_search' IN BOOLEAN MODE) ) 
$sql_within
HAVING relevance > 0 ORDER BY relevance DESC";

//if those fail, last ditch attempt

	$sql_3 = "
SELECT url, author, title, body, 
10*(
LENGTH(title) -
LENGTH(REPLACE(LOWER(title),LOWER('$sql_search'),'')))/LENGTH('$sql_search') 
+ 
10*(
LENGTH(author) -
LENGTH(REPLACE(LOWER(author),LOWER('$sql_search'),'')))/LENGTH('$sql_search') 
+ 
0.1*(
LENGTH(body) -
LENGTH(REPLACE(LOWER(body),LOWER('$sql_search'),'')))/LENGTH('$sql_search')
as relevance
FROM search
WHERE
( title REGEXP ' $sql_search ' OR body REGEXP ' $sql_search ' )
$sql_within
HAVING relevance>0
ORDER BY relevance DESC 
";

Thanks. What is the vartiable $sql_within?

dagon

if ($within != 'all') {
		$sql_within = "AND url like '%$within/%' ";
	}

$within is the site section (you can select when you search), which is in the url field of the search table,
most searches are $whithin is all, so $sql_within is usually just empty

bellow is how $sql_search is built

if ($matching == 'any') {
		$search_words = explode(' ', $search);
		$sql_search = $search;
	} 
	// exact
	elseif ($matching == 'exact') {
		$search_words['0'] = $search;
		$sql_search = '"' . $search . '"';
	} 
	// fuzzy
	elseif ($matching == 'fuzzy') {
		$search_words = explode(' ', $search);
		foreach ($search_words as $foo) {
			$sql_search .= '*' . $foo . '* ' ;
		} 
	} 

// //and search //defult //($matching == 'and')
else {
	$search_words = explode(' ', $search);
	$sql_search = '+' . implode(" +", $search_words);
}

if the whole thing would be of use to you i will send it to you off forum, its not that well written (5+ years old) i just though the bit posted could help.