Full-Text doesn't find the same # of rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Full-Text doesn't find the same # of rows

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2008-07-24 : 11:57:33

I am new to Full-Text search and learning about it.

For what I tried so far searching my db using Full-Text search is much faster than the where statements with 'like'

For instance

Select Ai_Id,Ai_Texto FROM  tblAvisosInternet
WHERE CONTAINS(Ai_Texto, '%Toyota% AND %Yaris%')
ORDER BY Ai_Id

takes less than 1 second to run

whereas

Select Ai_Id,Ai_Texto FROM  tblAvisosInternet
WHERE Ai_Texto like '%Toyota%' AND Ai_Texto like '%Yaris%'
ORDER BY Ai_Id

takes more than 4 seconds

But

I am trying to figure out why the version with 'like' comes up with 622 rows whereas the version with Full-Text search retrieves only 552 rows.
The difference being that Full-Text search does not seem to retrieve rows where words are "glued" together. Example the rows containing "TOYOTA YarisSport 2005...." is not listed whereas I am expecting it to be because it contains the word Yaris.

What am I missing ?

jean-luc
www.corobori.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 12:01:23

i think wildcard character in contains is *

http://doc.ddart.net/mssql/sql70/ca-co_15.htm

Corobori
Posting Yak Master

105 Posts

Posted - 2008-07-24 : 12:34:03

quote:
Originally posted by visakh16

i think wildcard character in contains is *

Actually the CONTAINS with or without wildcard retrieves the same number of rows.

jean-luc
www.corobori.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 12:52:33

quote:
Originally posted by Corobori

quote:
Originally posted by visakh16

i think wildcard character in contains is *

Actually the CONTAINS with or without wildcard retrieves the same number of rows.

jean-luc
www.corobori.com

What does this return?

Select Ai_Id,Ai_Texto FROM  tblAvisosInternet
WHERE CONTAINS(Ai_Texto, '"*Toyota*" AND "*Yaris*"')
ORDER BY Ai_Id

Corobori
Posting Yak Master

105 Posts

Posted - 2008-07-24 : 13:11:00

quote:
Originally posted by visakh16
What does this return?
Select Ai_Id,Ai_Texto FROM tblAvisosInternet
WHERE CONTAINS(Ai_Texto, '"*Toyota*" AND "*Yaris*"')
ORDER BY Ai_Id

Getting closer ! Now I am getting 571 rows; with this change I am now retrieving the rows containing "TOYOTA YarisSport 2005" but I am still missing the rows containing "TOYOTA NewYarisSport, año2007,"
I tried playing around with the wildcards, no luck so far.

jean-luc
www.corobori.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources