November 23, 2009 at 7:46 pm
Hi,
I have a table with multiple columns full-text indexed: Business Name, Category and City.
The search box of my application is open, so you can search any thing in the same field. When you write "Hotels in Santo Domingo" (without quotes) I'm suppose to search in every indexed field and give you the most relevant results, so I perform a search like this:
Select *
From Table1
Inner Join
CONTAINSTABLE(
Table1,
(BusinessName, Category, City),
'FORMSOF (INFLECTIONAL Hotels) AND FORMSOF (INFLECTIONAL Santo) AND FORMSOF (INFLECTIONAL Domingo)') FS
on Table1.Id = FS.
Order by FS.RANK desc
The value Hotels is present in the field Category, and the values Santo and Domigo are in City, but this query doesn't give me any results because it looks for the whole query on each column instead of all columns together.
I can do this merging all columns into one and indexing it and it will work, but I'm looking for a better solution.
Thanks in advance.
April 26, 2010 at 5:27 pm
I am trying to do something very similar. Did you come up with a solution to this?
So far all I can think to do is to use dynamic sql to break apart the string the user enters, search on each word individually and then use INTERSECT to create a list of records that are in each result set returned, but that seems a bit messy. Any ideas would be most helpful.
April 27, 2010 at 1:18 am
One common solution is to full-text index a computed column.
The following example happens to search for exact matches, but it can be easily modified to incorporate inflectional forms.
-- Test table
CREATE TABLE dbo.TestSearch
(
row_id INTEGER IDENTITY NOT NULL
CONSTRAINT [PK dbo.TestSearch row_id] PRIMARY KEY,
a VARCHAR(50) NOT NULL,
b VARCHAR(50) NOT NULL,
c VARCHAR(50) NOT NULL,
search AS
'¦' + a + '¦' + b + '¦' + c + '¦'
);
GO
-- Test data
INSERT dbo.TestSearch (a,b,c) VALUES ('Business1', 'Category1', 'City1');
INSERT dbo.TestSearch (a,b,c) VALUES ('Business1', 'Category2', 'City1');
INSERT dbo.TestSearch (a,b,c) VALUES ('Business1', 'Category3', 'City1');
INSERT dbo.TestSearch (a,b,c) VALUES ('Business2', 'Category1', 'City2');
INSERT dbo.TestSearch (a,b,c) VALUES ('Business2', 'Category3', 'City2');
INSERT dbo.TestSearch (a,b,c) VALUES ('Business3', 'Category2', 'City2');
GO
-- FT Catalog
CREATE FULLTEXT CATALOG TestCatalog
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo;
GO
-- FT Index
CREATE FULLTEXT INDEX ON dbo.TestSearch
(search LANGUAGE English)
KEY INDEX [PK dbo.TestSearch row_id] ON TestCatalog
WITH CHANGE_TRACKING AUTO;
GO
-- Wait for population
WAITFOR DELAY '00:00:02';
GO
-- Search
SELECT CT.[RANK],
TS.a,
TS.b,
TS.c
FROM CONTAINSTABLE(dbo.TestSearch, search, N'¦Business2¦Category1¦City2¦', 50) CT
JOIN dbo.TestSearch TS
ON TS.row_id = CT.;
GO
-- Clean up
DROP FULLTEXT INDEX ON dbo.TestSearch;
GO
DROP FULLTEXT CATALOG TestCatalog;
GO
DROP TABLE dbo.TestSearch;
GO
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2010 at 12:15 pm
Thanks, unfortunately the tables that I am searching are very large and some of the columns have lengthy text so creating the fulltext index on the fly takes a long time.
In my specific situation I have a database of aprox. 100,000 organizations. There are about 14 fields in 6 tables that need to be included in our keyword search, and the search should return the organizations that match on all of the terms entered.
So for example if a user enters "english girls school egypt" my search needs to return organizations that have all four keywords, but they don't need to have all four in the same column or even in the same table. You can imagine "Egypt" might be in a country look up table, "School" might be in the name of the organization or possibly in the name of the category that the organization is classified as, "girls" or "girl" could be in a description field, etc.
So here is what I came up with so far (I took the loop idea from this article)
http://www.sqlservercentral.com/articles/Miscellaneous/2875/
It works and solve the problem but it is still in need of help performance wise if anyone has any suggestions they would be most appreciated 🙂
DECLARE
@KW varchar(50),
@count as bit
IF (CHARINDEX(',', @kws) > 0) -- more than one search term entered
BEGIN
CREATE TABLE #TT(ID int)
Set @count = 0
WHILE (CHARINDEX(',', @kws) > 0)
BEGIN
-- Get the individual keyword from the string
SET @KW = 'FORMSOF(INFLECTIONAL, "' + CAST(SUBSTRING(@kws, 0, CHARINDEX(',', @kws)) AS VARCHAR(100)) + '")'
-- search on @KW and insert returned ID's to the temp table
if (@count = 0)
BEGIN
/* now put together the list and insert it into the temp table */
INSERT INTO #TT
SELECT ID FROM table1 WHERE CONTAINS(*, @KW)
UNION
SELECT ID FROM table2 WHERE CONTAINS(*, @KW)
UNION
SELECT ID FROM table2 WHERE CONTAINS(*, @KW)
--UNION SELECT ... etc.
SET @count = 1
END
ELSE
BEGIN
-- second, third, forth etc. search word
-- delete any rows from the temp table that do not also match on the next word entered.
DELETE FROM #TT WHERE #TT.ID NOT IN (
SELECT a.ID FROM(
SELECT ID FROM table1 WHERE CONTAINS(*, @KW)
UNION
SELECT ID FROM table2 WHERE CONTAINS(*, @KW)
UNION
SELECT ID FROM table2 WHERE CONTAINS(*, @KW)
--UNION SELECT ... etc.
) AS a )
END
-- update the keyword string
SET @kws = SUBSTRING(@kws, CHARINDEX(',', @kws) + 1, LEN(@kws))
END -- end while
-- return the list of matching organizations
SELECT * from #TT
END -- end if more than one search term
ELSE -- just one search term no need for the temp table or loop
BEGIN
SET @KW = 'FORMSOF(INFLECTIONAL, "' + @kws + '")'
SELECT ID FROM table1 WHERE CONTAINS(*, @KW)
UNION
SELECT ID FROM table2 WHERE CONTAINS(*, @KW)
UNION
SELECT ID FROM table2 WHERE CONTAINS(*, @KW)
--UNION SELECT ... etc.
END
April 29, 2010 at 2:14 am
In my opinion this is rather a design problem. Try to redesign your query like this, for example:
SELECT <column_list> FROM <table> WHERE CategoryID = <category id for "hotels"> AND CONTAINS(City, 'Santo AND Domingo')
April 29, 2010 at 4:56 am
Since all of the keywords are passed in as one parameter
for example @kws = "english girls school egypt"
there is no way of telling which word from the string should be matched to which column / table. I need to check every word with every column that needs to be included in the search.
April 29, 2010 at 6:15 am
alicia-694427 (4/28/2010)
Thanks, unfortunately the tables that I am searching are very large and some of the columns have lengthy text so creating the fulltext index on the fly takes a long time.
My reply was aimed at the original questioner.
Your case is different - though I wish you had started your own thread for this and provided some example table definitions, data, and expected output.
You will never get good performance from the looping idea. Get the data into one place and full-text index it there. If you require real-time querying, use triggers or an indexed view.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply