On 10/11/05, Daniel Kessler <[EMAIL PROTECTED]> wrote: > As y'all have read, I'm trying to replicate a google-like search on > our database. I think that I have the CF and DB (Oracle) logic > worked out but I'd like someone else to look it over and see if it's > right. I want to search the same way that google does since most > people are used to that. I want words separated by spaces, quoted > text (a phrase) treated as a single word, + meaning that it must have > the word and - that it must not contain the word. > > Here's the search page which allows for a search of a few different > columns: > http://hhp.umd.edu/people/people_search.cfm > > and here's the code that I use (and any improvements are welcome): > > > <!--- > before this, I make the three arrays described here: > - search_ar: the OR search items > - search_must: the AND "must contain" items, designated > as + before the word > - search_not: the items that "must not contain" in the > returned results, designated as minus before the word > ---> > > <CFQUERY NAME="people_search" DATASOURCE="dpch"> > SELECT > id,fname,lname,type,title,dept,phone,location,email,summary,imagePath,li > nk > FROM PEOPLE > WHERE > <cfloop from="1" to="#arrayLen(search_ar)#" index="ii"> > UPPER(#PreserveSingleQuotes(the_search_column)#) > LIKE UPPER('%#search_ar[ii]#%') > <cfif ii LT arrayLen(search_ar)>OR</cfif> > </cfloop> > <cfif arrayLen(search_ar) GT 0 AND arrayLen > (search_must) GT 0>AND</cfif> > <cfif arrayLen(search_must) GT 0> > ( > <cfloop from="1" to="#arrayLen(search_must)#" > index="jj"> > UPPER(#PreserveSingleQuotes > (the_search_column)#) LIKE UPPER('%#search_must[jj]#%') > <cfif jj LT arrayLen(search_must)>AND</cfif> > </cfloop> > ) > </cfif> > <cfif (arrayLen(search_must) GT 0 OR (arrayLen > (search_ar) GT 0 AND arrayLen(search_must) EQ 0)) AND arrayLen > (search_not) GT 0>AND</cfif> > <cfif arrayLen(search_not) GT 0> > ( > <cfloop from="1" to="#arrayLen(search_not)#" > index="kk"> > UPPER(#PreserveSingleQuotes > (the_search_column)#) NOT LIKE UPPER('%#search_not[kk]#%') > <cfif kk LT arrayLen(search_not)>AND</cfif> > </cfloop> > ) > </cfif> > > <!--- ---> > > AND status = 'active' > ORDER BY upper(lname) ASC > </CFQUERY> >
It might have a Google "feel" from a UI standpoint, but your code above will more than likely not scale at all. First, when you use Oracle functions around column names such as: UPPER(column) = '#UCase(myvalue)#' Oracle will not use any indexes defined on that column, which means it's going to do a full table scan for that value (translation: not scalable). Second, LIKE queries, in general, should be avoided, particularly for heavy search queries. You should really look into using Oracle's Full Text Search capabilities, as the performance difference can be dramatic. It's a little more difficult to implement up front, but that effort will go a long way to saving your database. Some might also say that you should be using cfqueryparam as well so that bind parameters are used, but I've read in several places that user-defined search queries like you have are actually *better off* without bind parameters because you don't risk Oracle creating an initial explain plan for a query based on one set of terms terms, only to find out that another set of search terms should use a completely different explain plan. I don't know what the traffic at that site is like, nor do I know the number of records your searching against (or will search against in the future), but I think you should do some more research into Full Text Searching before committing to your current approach, as I think you're setting yourself up for trouble, if not now, down the road. Regards, Dave. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220663 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
