sql server 2005 - SQL Keyword search algorithm: This SQL does a sequential search, how to do an indexed search? -


we have 3 tables hold our products , keywords:

product {int id, string name, ...} productkeyword {int productid, int keywordid} keyword {int id, string keyword}

this sql code returns relevant products least relevant products having keywords users search criteria. searchwordtable table of search words. @keywordcount count of search words. returns products having 1 or more keywords, ordered quantity of keywords found each product.

select productid, productname, count(*) * 1 / @keywordcount percentrelevant (select keyword, productid, productname         product              join productkeyword on ...               join keyword on ...              join searchwordtable on searchwordtable.keyword                    '%' + keyword.keyword + '%') k -- join aweful group productid, productname order percentrelevant desc -- relevant first

the problem sequential search comparing every keyword have. it's not bad, searches can take minute million records.

how rewrite query not use like, use indexed search, , similar results? use like partial matches, e.g., 'bone' in 'boneless'.

links better sql algorithms surely appreciated.

like killing you, leading wildcard removes indexes columns may have.

you should using sql server's full-text indexing. contains faster , far more powerful partial matches of nature appear doing.


Comments

Post a Comment

Popular posts from this blog

asp.net - repeatedly call AddImageUrl(url) to assemble pdf document -

i'm using abcpdf , i'm curious if can recursively call addimageurl() function assemble pdf document compile multiple urls? something like: int pagecount = 0; int theid = thedoc.addimageurl("http://stackoverflow.com/search?q=abcpdf+footer+page+x+out+of+", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetochain(theid); } pagecount = thedoc.pagecount; console.writeline("1 document page count:" + pagecount); //flatten document (int = 1; <= pagecount; i++) { thedoc.pagenumber = i; thedoc.flatten(); } //now try again theid = thedoc.addimageurl("http://stackoverflow.com/questions/1980890/pdf-report-generation", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetoc...
Read more

java - Android recognize cell phone with keyboard or not? -

in android app, want detect user's device if device has keyboard(like motorola milestone) , show corresponding user interface. guys know how that? ! regards justicepenny http://developer.android.com/reference/android/content/res/configuration.html public int keyboard the kind of keyboard attached device. 1 of: keyboard_nokeys, keyboard_qwerty, keyboard_12key. that's it!
Read more

iphone - How would you achieve a LED Scrolling effect? -

how achieve led scrolling effect example below? led sign - led ticker emulator iphone , ipad http://img.skitch.com/20101201-rsfh4p1bajb1wp94k466pdpiuj.preview.jpg sometimes remunerates comment in other posts :) have put code you, should implement font '8pin matrix' realistic feeling. uilabel *label; - (void)viewdidload { [super viewdidload]; label = [[uilabel alloc]initwithframe:cgrectmake(10, 50, 300, 20)]; label.font = [uifont fontwithname:@"courier-bold" size:16.0]; label.backgroundcolor = [uicolor blackcolor]; label.textcolor = [uicolor greencolor]; label.linebreakmode = uilinebreakmodecharacterwrap; // otherwise "…" label.text = @"this led text scrolled "; // blanks space between [self.view addsubview:label]; [nstimer scheduledtimerwithtimeinterval:0.12f target:self selector:@selector(scrolllabel) use...
Read more