May 15, 2002 at 10:40 pm
Hi All,
I would like to know if any other SQL queries can be performed to fetch the search results from a table much faster, other than the following SQL query.
select * from Books where BOOK_TITLE like '%programming%'
Any help will be much appreciated..
Thanks
Naveen
Naveen M
Naveen M
May 16, 2002 at 4:41 am
Using a leading wild card is going to slow things down. Sometimes in cases where you need it full text search is a decent option.
Andy
May 16, 2002 at 5:17 am
Likes are naturally slower anyway but do perform better if you can avoid doing LIKE '%something%' and instead doing LIKE 'something%'. The only faster thing but adds a lot of overhead to the server (especially maintainence) is to use the Full Text Serach engine. However another way you can speed things up is to generate a cheet sheet for common searched words. Basically you place a trigger on your main table and when a record goes in with a long text field (varchar, char, text, etc) You parse the words and insert into a new table in seperate columsn with the PK index value of the record they exist in. Note you can multiple possiblities of the word, which ou keep each and you setup a clustered index on this field of your cheat sheet table. Then you can create a query to check for the wod and what records it occurrs in. It can also be done with cobinations such as "programming databases" but it gets quite tricky and I suggest you check for "programming" and "databases" seperately and find the common PK values between them.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 16, 2002 at 8:40 am
Antares, great response!
Make it an article, you already posted a couple of FAQ on this subject, it seems like you know what you are talking about!
Usually forum's responses are getting lost somewhere. I tried to locate the old response a couple of times and it was a pain!
This a very interesting explanation to be lost.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply