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?
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.
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?
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
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.
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.