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.
| Author |
Topic |
|
piehawk
Starting Member
4 Posts |
 Posted - 2004-08-24 : 05:01:02
|
| hi chapslooking for a little advice on best way to search.got an online products database and there is a keyword search which will search through 4 fields for the phrase.rather than doing an "or" on each of the fields, i've simply appended them all together in the where clause of the select statement and then done a like on this.egwhere category + ' ' + reference + ' ' + description + ' ' + metal + ' ' + keywords like '%gemstone%'all of these fields are indexed in the database for optimised searching, but the question is.....would it better for me to create a single "search" field in the database which already has these fields appended together and then index that and use solely that for the search?cheerspie |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-24 : 05:12:44
|
| using '%searchword%' kind of "disables" the indices... read it here somewhere....wouldn't full text search be faster?Go with the flow & have fun! Else fight the flow :) |
 |
|
|
piehawk
Starting Member
4 Posts |
Posted - 2004-08-24 : 05:47:18
|
| thanks for the reply.haven't read that about it disabling the indices before.using a shared sql server and they don't allow me to access the Full Text Index feature.tapie |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-24 : 05:53:01
|
| it doesn't disable them. it just doens't use them... i'll try to find that thread...this is a good article on serching:http://www.sqlteam.com/item.asp?ItemID=1876Go with the flow & have fun! Else fight the flow :) |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-08-24 : 09:47:03
|
| That is correct, a query with a wildcard at the beginning of the search expression cannot use an index. To give you an analogy, think of an index at the back of a book, if you do not know the first letter of the word you are searching for, can you use that index? It requires you to go through each entry in the index until you find what you are looking for. In SQL Server, that is exactly the difference between an Index Seek (good) and Index Scan (bad).OS |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-24 : 09:56:20
|
| i wonder if inices would work in this way:where col like 'blah%' - this uses them.where reverse(col) like 'halb%' - would this work? i mean would it use an index?just an idea... :))Go with the flow & have fun! Else fight the flow :) |
|
|
|
|
|
|
Best way to keyword search multiple columns