• quickdraw (2/28/2011)


    #3 By reversing the kewords in the keywords table, you get a new feature no database has (without a full table scan)

    One additional thing I did is to reverse all the keywords in the keywords table. Since Wildcard LIKE searches were allowed on the keywords table (like in full text search), having each keyword spelled backwards allowed a wonderful feature:

    ...

    Normally for a wildcard search to use an index, the wildcard can't be at the begining (ex: giv%, matching give, giving, giver), otherwise a full table scan results on the keyword table. With a keyword table containing hundreds of thousands of rows that slows things down considerably. Reversing the keywords in the keyword table allows the keyword parser to detect wildcards and construct a where clause that includes the keyword reversed:

    select <cols> from document where docid in (

    select docid from dockeyword where keywordid IN (SELECT keywordid from keyword where keyword LIKE 'hold')

    union

    select docid from dockeyword where keywordid IN (SELECT keywordid from keyword where keyword LIKE 'her')

    union

    select docid from dockeyword where keywordid IN (SELECT keywordid from keyword where reversekeyword LIKE 'yl%' --note keyword spelled backwards)

    )

    That is a great suggestion, reversing keywords. Nice.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP