Hi,
I have a large table with millions and millions of records all related to
web traffic. I am trying to match certain records based on a query string
variable; say varname.
If I use the like operator it is SELECT ... FROM PageView WHERE QueryString
LIKE '%varname=%'
I was hoping to get better performace out of this using full text indexing
but I am not sure it will be able to accomodate my needs. The string string
I am searching in can have other things before and after and rarely any
spaces in it: Like 'abc=123&varname=xxx&ggg=000' and many other variations.
Will full text indexing help in such cases?
Do I need to define certain characters as word seperators for this type of
index (in a query string it will be the '&' sign).
Thanks
Hello,
I understand that you'd like to use full-text search to improve perofrmance
of query with clause such as "LIKE '%varname=%'". If I'm off-base, please
let me know.
Fulltext index is based on key words of the document stored in table. The
word breaker split work of the documents by white space and word separators
for this locale. If the key word you search could be properly splited by
word breaker, you could use full-text search. For exmaple:
select * from tbl where varname= 'data'
"varname=" might be splited correctly by word breaker.
Howerver, the following string might not be splited properly:
select * from tbl where varname='data'
Also, you may use wildcard to search the word with prefix you want. For
example:
Select * from table contains (columnname, '"varname*"')
This query can return varname1, varname=...
However, you could use wildcard * before "varname" and it does not take
efffect. This is a limitation of SQL full-text search.
You could refer to the following articles for more related information:
Full-Text Search Fundamentals
http://msdn2.microsoft.com/en-us/library/ms142581.aspx
200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
Queries
http://support.microsoft.com/?id=200043
Word Breaker and Stemmer Sample
http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_3e91.asp?f
rame=true
Implementing a Word Breaker
http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_54bp.asp?f
rame=true
271818 How to configure Windows 2000 Indexing Service to use the Neutral
word
http://support.microsoft.com/?id=271818
If you have further questions or concerns, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks for the great response.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:8z82E3skHHA.5432@.TK2MSFTNGHUB02.phx.gbl...
> Hello,
> I understand that you'd like to use full-text search to improve
> perofrmance
> of query with clause such as "LIKE '%varname=%'". If I'm off-base, please
> let me know.
> Fulltext index is based on key words of the document stored in table. The
> word breaker split work of the documents by white space and word
> separators
> for this locale. If the key word you search could be properly splited by
> word breaker, you could use full-text search. For exmaple:
>
> select * from tbl where varname= 'data'
> "varname=" might be splited correctly by word breaker.
> Howerver, the following string might not be splited properly:
> select * from tbl where varname='data'
> Also, you may use wildcard to search the word with prefix you want. For
> example:
> Select * from table contains (columnname, '"varname*"')
> This query can return varname1, varname=...
> However, you could use wildcard * before "varname" and it does not take
> efffect. This is a limitation of SQL full-text search.
> You could refer to the following articles for more related information:
> Full-Text Search Fundamentals
> http://msdn2.microsoft.com/en-us/library/ms142581.aspx
> 200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
> Queries
> http://support.microsoft.com/?id=200043
> Word Breaker and Stemmer Sample
> http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_3e91.asp?f
> rame=true
> Implementing a Word Breaker
> http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_54bp.asp?f
> rame=true
> 271818 How to configure Windows 2000 Indexing Service to use the Neutral
> word
> http://support.microsoft.com/?id=271818
> If you have further questions or concerns, please feel free to let's know.
> Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
