Too many records with fulltext search

Oddish · #1 (**permalink**) 04-24-07, 04:32

I'm trying to create a simple search function and noticed that when there are too many matching rows, nothing is returned. Is there any way to remove this limitation, or at least some way to find out if too many rows were returned? How do I know if there were 0 matching rows, or too many matching rows? MATCH() returns 0 in both cases.

aschk · #2 (**permalink**) 04-24-07, 05:07

What is the sql statement that you are executing??

guelphdad · #3 (**permalink**) 04-24-07, 23:20

i'm not sure about full text indexing, but I know that a regular index ignores search terms that appear in 50% or more of the rows. there is no way to change that that I am aware of. full text indexing may be the same.

Oddish · #4 (**permalink**) 04-25-07, 03:36

Quote:

Originally Posted by aschk

What is the sql statement that you are executing??

The match part in the "where" clause is

Code:

AND
	MATCH (
		s.body,
		s.address,
		s.zip,
		s.city
	) AGAINST(
		'search phrase'
	)
";

But anyway, I'll just write something like "Your search returned no results, or matched more than 50 % of the records".

aschk · #5 (**permalink**) 04-25-07, 07:23

In order to by-pass the 50% limitation you need to use a BOOLEAN match in your search. See : http://dev.mysql.com/doc/refman/5.0/...t-boolean.html

So ideally what you should be doing is this :

Code:

AND
	MATCH (
		s.body,
		s.address,
		s.zip,
		s.city
	) AGAINST(
		'search phrase'
	)
       IN BOOLEAN MODE
";

This will find any results which match your search pharse with at least one word from the search phrase.

Oddish · #6 (**permalink**) 04-25-07, 10:45

Quote:

Originally Posted by aschk

In order to by-pass the 50% limitation you need to use a BOOLEAN match in your search.

Wow, that was easy! Thanks a lot!

Oddish · #7 (**permalink**) 05-04-07, 07:27

I've been thinking about this, and I don't really se any real world use for fulltext search. I'd bet that 99% of users expect to find "theword" if they search for "ewor", and this is apparently impossible with fulltext search. Now I'm forced to build a custom engine with LIKE statements...

Internet Services

Web Development

Digital Marketing

Consumer Tech