Results 1 to 12 of 12
-
05-24-2004, 06:46 PM #1Aspiring Evangelist
- Join Date
- May 2004
- Posts
- 351
microsoft sql server text search?
Hello,
I am thinking about writing a script that will utilize a Microsoft SQL Server database. One of the fields in the table will be a field containing text only. I have never done a search query on a text field before so I had a few questions that I would really appreciate answers to:
1. What is the performance hit of doing a search for a word on a text field?
2. Is this what the Full Text search feature in MSSQL is for or can this be done using some other query method?
3. What kind of query would I use? For example, if I wanted to search for a record containing the words "john" and "doe" in its text field, what kind of query would I use?
Thanks in advance
-
05-24-2004, 07:00 PM #2Web Hosting Master
- Join Date
- May 2002
- Location
- UK
- Posts
- 2,997
Are you familiar with SQL? For the most part MS SQL works in the same way as other SQL compliant databases, sure they all have their quirks but the core is the same.
It all really depends on how well designed your database is for what type of performance hit you get. If you have a well designed database with decent indexing then a keyword search really won't be a problem.
Yes MS SQL has full text search, however it also has the very cool, natural language querying.
-
05-24-2004, 09:12 PM #3Aspiring Evangelist
- Join Date
- May 2004
- Posts
- 351
I am familiar with SQL. Though mostly I have used mysql, I have made use of mssql before. However, I have never really done keyword searches on the databases because it was either not needed, or the pages didnt change so often that a regular search engine would have a problem.
Now I am working on a database that will be changing constantly in a shared environment so I am apprehensive to test my queries on it before I can estimate its performance impact.
Also, what would indexing a text field accomplish?
Thanks again
-
05-24-2004, 10:03 PM #4Web Hosting Guru
- Join Date
- Jan 2003
- Posts
- 261
SELECT Field, SomeField From Table Where SomeField LIKE '%$string1%' OR LIKE '%$string2%'
Text searches aren't as fast as primary key id fields. For example if you are doing a left join of sorts it would take quite a bit longer if you were matching similar text than if you were just matching Table1.ID and Table2.ID.
For the love of God, use Oracle, MySQL, or PostgreSQL... don't support the evil empire
-
05-25-2004, 12:03 AM #5Aspiring Evangelist
- Join Date
- May 2004
- Posts
- 351
heh
I was also investingating the LIKE statements and I guess I will do some microbenchmarking on LIKE vs. indexing using full text search.
The empire is evil, but once it gets some competition, it gets a bit better.
I dont know oracle, never used it, dont have access to it therefore Oracle is a non factor.
Mysql is fine, but is still lacking featurewise. It is also slower in many instances (though you could say the same for any DB).
-
05-25-2004, 01:26 AM #6Web Hosting Guru
- Join Date
- Jan 2003
- Posts
- 261
You can use Oracle for free for development. Its just a massive download from their site is all. Then if you decide to put it on a production server you get to pay through the nose like for anything Microsoft.
-
05-25-2004, 04:50 AM #7Web Hosting Master
- Join Date
- Jul 2003
- Location
- Kuwait
- Posts
- 5,104
Along the same lines, you can get IBM's DB2 for testing and development for free also -- although I wouldn't hold by breath while downloading it -- its a 2 gig+ download.
-
05-25-2004, 05:15 AM #8Aspiring Evangelist
- Join Date
- May 2004
- Posts
- 351
jeebus. I still dont understand why they are so huge when mysql is like 20 megs. Do they have 200 times the features?
ps: oracle will be a more interesting options once microsoft finishes their plans for the oracle .net provider which they have announced.
-
05-25-2004, 06:56 AM #9Web Hosting Master
- Join Date
- May 2002
- Location
- UK
- Posts
- 2,997
Actually LIKE statements are MUCH slower if you use % at both ends because of the way that indexing works.
Microsoft's natural language querying system in MS SQL server doesn't have any other competition with the other products, so if that is important to you, MS SQL is your only option.
MySQL does not support the huge extra features that the big database do. Although MySQL are gradually adding all the groovy stuff in, many of the top level features still aren't there.
-
05-26-2004, 06:13 PM #10Aspiring Evangelist
- Join Date
- May 2004
- Posts
- 351
I wonder how mysql forums do so well in search times when they mostly use LIKE statements in the sql? (I belive)
Anyone have some insight into this?
-
05-26-2004, 06:26 PM #11Web Hosting Master
- Join Date
- May 2002
- Location
- UK
- Posts
- 2,997
Well vBulletin generates it's own index format so the like statement (if it even uses one) so it can be much faster than running a LIKE search across the body of the entire message.
-
05-26-2004, 07:08 PM #12Aspiring Evangelist
- Join Date
- May 2004
- Posts
- 351
I just looked at phpbb and it also uses a similar self generated index. I think I may take a look at this method since full text indexing seems overkill (I dont need inflection or many of the other features). Just for kicks though, I think I will experiment with the search indexing service. I just read the whitepaper and it has a few nifty tracking self update features.
Thanks again for all the info