Best practice regarding searching keywords with sql

Question

I have three tables

[USER] --Master user table 
[KEYWORD] --Master keyword table  
[USER_KEYWORD] --[USER]-[KEYWORD] mapping table

Below is the structure in my db

GO
--master user table
CREATE TABLE [USER]
(
    [USERID] INT IDENTITY,
    [NAME] VARCHAR(50)
)
GO

--master keyword table
CREATE TABLE [KEYWORD]
(
    [KEYWORDID] INT IDENTITY,
    [KEYWORD] VARCHAR(50)
)

GO
--x table user_keyword
CREATE TABLE [USER_KEYWORD]
(
    [USERID] INT ,
    [KEYWORDID] INT
)

GO
--Insert data in master user table
INSERT INTO [USER]
SELECT 'TESTUSER1'
UNION ALL
SELECT 'TESTUSER2'
UNION ALL
SELECT 'TESTUSER3'
UNION ALL
SELECT 'TESTUSER4'

GO
--Insert data in master keyword table
INSERT INTO [KEYWORD]
SELECT 'ASP'
UNION ALL
SELECT 'ASP.NET 3.5'
UNION ALL
SELECT 'C#'
UNION ALL
SELECT 'JAVA'
UNION ALL
SELECT 'ASP.NET'
UNION ALL
SELECT 'SQL'
UNION ALL
SELECT 'SQL SERVER'
UNION ALL
SELECT 'SQL SERVER 2005'
UNION ALL
SELECT 'SQL SERVER 2008'

GO

--Insert data in user keyword table

INSERT INTO [USER_KEYWORD]
SELECT 1,1
UNION ALL
SELECT 2,2
UNION ALL
SELECT 3,3
UNION ALL
SELECT 4,4
UNION ALL
SELECT 1,2
UNION ALL
SELECT 2,3
UNION ALL
SELECT 3,4
UNION ALL
SELECT 4,1
UNION ALL
SELECT 2,3
UNION ALL
SELECT 3,4
UNION ALL
SELECT 4,6
UNION ALL
SELECT 3,6
UNION ALL
SELECT 4,6
UNION ALL
SELECT 2,7
UNION ALL
SELECT 3,8
UNION ALL
SELECT 4,9

GO


CREATE PROC TEST_SEARCH_KEYWORDS
@SEARCHKEYWORD VARCHAR(50)
AS
BEGIN

    SELECT K.[KEYWORD],COUNT(UK.[KEYWORDID]) AS [KEWWORDCOUNT] FROM [KEYWORD] K 
    INNER JOIN [USER_KEYWORD] UK
    ON K.[KEYWORDID]=UK.[KEYWORDID]
    WHERE K.[KEYWORD] LIKE (@SEARCHKEYWORD+ '%')
    GROUP BY K.[KEYWORD]

END


--TEST EXAMPLES

EXEC  TEST_SEARCH_KEYWORDS 'ASP'
--0UTPUT
KEYWORD                                            KEWWORDCOUNT
-------------------------------------------------- ------------
ASP                                                2
ASP.NET 3.5                                        2


--TEST EXAMPLES

EXEC  TEST_SEARCH_KEYWORDS 'SQL'
--0UTPUT
KEYWORD                                            KEWWORDCOUNT
-------------------------------------------------- ------------
SQL                                                3
SQL SERVER                                         1
SQL SERVER 2005                                    1
SQL SERVER 2008                                    1

I have one sp named *TEST_SEARCH_KEYWORDS* to search records based on keywords that is provided from outside.Currently i am using like condition in sp. I want to know that is this will be good to search records from database using like as in future my records will keep on increasing .I don't want to use full text search as i have to cope with sql server 2000 also.

Answer 1

One (storage-hungry) solution would be to pre-compute and store a number keyword prefices alongside the actual keyword itself. Then based on the look criteria you would do a direct equality comparison on the relevant prefix column (if one existed) or else fall back to performing a "like" comparison.

For example, suppose to impose a limit that the search word must contain at least 3 characters. You could create columns Prefix3Chars, Prefix4Chars, Prefix5Chars. Say the user enters the search word "Hello" and your database contained a key word "Hello, World". You would elect to search on the Prefix5Chars column and would match against the key word "Hello, World". If the user performed a search with key word "Hello, W" you would fall back to searching using "like".

Answer 2

if you keep searching like:

WHERE K.[KEYWORD] LIKE (@SEARCHKEYWORD+ '%')

then your query will use an index, and should still perform well as the table grows. However, if you change it to:

WHERE K.[KEYWORD] LIKE ('%'+ @SEARCHKEYWORD+ '%')

then it will not, and it will table scan, which will cause bad performance as your table grows.