what is the best ways for search engine

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

what is the best ways for search engine

Author

Topic

querybest
Starting Member

22 Posts

Posted - 2005-08-11 : 12:22:44

hello

Could anyone please explain how to create best searching query ?

if my table has 1 million records and I use the query below
"select * from mytable where desciption like '%sql%'"
it is deadly :)

How can I do ?

please give me suggestion like "you must views" ..

I am also using sql precedures but it is same too

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-11 : 12:27:58

First, never use SELECT *. Explicitly, define only the fields even if you need all of them.

Second, do you have to use like and the wildcards? If not dump them in favor of an = sign.

What's the field type of description? Have put an index on this field?

====================================================
Regards,
Sean Roussy

Thank you, drive through

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-08-11 : 13:38:26

>>Second, do you have to use like and the wildcards? If not dump them in favor of an = sign.

the problem is not in LIKE but in the first wildcard '%sql%' - with such query MSSQL cannot use index, it must scan the whole table. If you really need this query - consider fulltext search

querybest
Starting Member

22 Posts

Posted - 2005-08-11 : 14:28:12

thank you.

the type of desctiption is "text"
because records have long text (for each one approximately 2-3 kb)

and unfortunately I have to use like '%xx%'

I don't know anything about fulltext search. I have heard of it but I am afraid I think..

I could not find any clearly article that explains everything about fulltext search.
If you can help me with it, it will be wonderful.

**>> First, never use SELECT *.

although I need all fields, should I define them instead of * ?
it seems not to be difference

thank you..

I am waiting for your solutions about fulltext search.
how can I start it

nadejda18
Starting Member

7 Posts

Posted - 2005-08-11 : 17:00:12

Did you see this article?
http://www.databasejournal.com/features/mssql/article.php/3441981
http://www.databasejournal.com/features/mssql/article.php/3454281
http://www.databasejournal.com/features/mssql/article.php/3467591
http://www.databasejournal.com/features/mssql/article.php/3486331

querybest
Starting Member

22 Posts

Posted - 2005-08-11 : 23:20:29

thank you for links..

I am trying..
But at the beginning I stopped :)

when I try to create Full text index, I get the following error

"The Microsoft Search service cannot be administered under the present user account"

I searched google about this error.. nothing was helpful.

please look at the image below
(first 2 images from Services>Microsoft Search service)
(next 2 from services>Microsoft Sql)
(next 2 from Enterprise manager>Security tab)
(last 2 from after clicking finish button in create full text search tool)

querybest
Starting Member

22 Posts

Posted - 2005-08-12 : 01:58:56

guys, thank you so much.

I have the same problem with Full text search creating

but I tried another computer.

It is very good ;)

But I need another question..

I will use full text search in ASP.

but how can I paging results ?

normal things dont work in ful text search because of inner join I think..

P.S : I prefer FREETEXTTABLE ;)

querybest
Starting Member

22 Posts

Posted - 2005-08-12 : 02:52:42

http://66.102.9.104/search?q=cache:wQG706SXLOUJ:www.devhome.org/asp/+paging+asp+getrows

this will be help full about paging records with full text search for ASP

querybest
Starting Member

22 Posts

Posted - 2005-08-12 : 05:16:17

hi again.

I am confused.
I have another table that has 160.000 records..
I don't search in it with LIKE..

table structure is like below

ID(autonumber)|M_ID(varchar)|DEscription(varchar)

I call records like that "select * from tbl where M_ID='lp333256'"

in this point what I want to ask is that
for it should I use full text search ?
does full text search affect my queries performance positively?

I hope so :)

I like full text search so much :)

thank you guyss!!!

querybest
Starting Member

22 Posts

Posted - 2005-08-12 : 06:16:11

can't INT fiels be indexed ? :(:(:(

I have a table like below

ID|from |to |Country
1 |100663296|121195295|Usa
.....
.....
.....

there are 56.000 intervals for all countries.
I am checking user's ip in table to learn where visitor comes..

I need it is indexed

any suggestion?

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-08-12 : 09:14:41

>>for it should I use full text search ?

you should use fuultext search for queries like 'select * form mytable where myfield = '%xxx%''

>>I need it is indexed

of cause you can index it, what's the problem ?

querybest
Starting Member

22 Posts

Posted - 2005-08-12 : 20:23:41

I need it is indexed by its int fields.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources