代码之家  ›  专栏  ›  技术社区  ›  Mohamad

使用MySQL和ColdFusion编写优化高效的搜索引擎

search-engine coldfusion search mysql
  •  2
  • Mohamad  · 技术社区  · 15 年前

    我有一个搜索页面,下面列出了以下场景。有人告诉我,使用这么多条件语句会降低性能,但我不知道是否有其他方法可以实现相同的目标。

    1. search.cfm将使用一个搜索输入(titlename)处理从所有页面上的搜索栏进行的搜索。
    2. 如果手动访问search.cfm(通过url而不是通过在所有页面上使用简单的搜索栏),它将显示一个高级搜索表单,其中包含三个输入(titlename、genreid、platformid(动态生成,但不在本例代码中),或者它评估searchresponse变量并决定要显示页面的哪个部分。
      1. 如果简单搜索/高级搜索查询为空、没有结果或少于3个字符,则显示错误。
      2. 如果任何成功的搜索返回结果,它们将正常返回。有一个函数处理查询。

    页面顶部逻辑如下: 

        <!---DEFINE DEFAULT STATE--->
<cfparam name="variables.searchResponse" default="">

<!---CHECK TO SEE IF SEARCH A FORM WAS SUBMITTED AND EXECUTE SEARCH IF IT WAS--->
<cfif IsDefined("Form.simpleSearch") AND Len(Trim(Form.titleName)) LTE 2>
    <cfset variables.searchResponse = "invalidString">
<cfelseif IsDefined("Form.simpleSearch") AND Len(Trim(Form.titleName)) GTE 3>
    <cfinvoke component="gz.cfcomp.search" method="searchTitles" titleName="#Form.titleName#" genreID="" platformID="" returnvariable="searchResult">
    <cfset variables.searchResponse = "hasResult">
</cfif>

<!---CHECK IF ADVANCED SEARCH FORM WAS SUBMITTED--->
<cfif IsDefined("Form.advancedSearch") AND (Len(Trim(Form.titleName)) LTE 2 AND Len(Form.genreID) IS 0 AND Len(Form.platformID) IS 0)>
    <cfset variables.searchResponse = "invalidString">
<cfelseif IsDefined("Form.advancedSearch") AND variables.searchResponse IS NOT "invalidString">
    <cfinvoke component="gz.cfcomp.search" method="searchTitles" returnvariable="searchResult" titleName="#Form.titleName#" genreID="#Form.genreID#" platformID="#Form.platformID#">
    <cfset variables.searchResponse = "hasResult">
</cfif>

<!---CHECK IF ANY RECORDS WERE FOUND--->
<cfif IsDefined("variables.searchResult") AND searchResult.RecordCount IS 0>
    <cfset variables.searchResponse = "noResult">
</cfif>

    我正在使用searchresponse变量根据以下情况决定页面显示的内容: 

    <!---ALWAYS DISPLAY SIMPLE SEARCH BAR AS IT'S PART OF THE HEADER--->
<form name="simpleSearch" action="search.cfm" method="post">
<input type="hidden" name="simpleSearch" />
<input type="text" name="titleName" />
<input type="button" value="Search" onclick="form.submit()" />
</form>

<!---IF NO SEARCH WAS SUBMITTED DISPLAY DEFAULT FORM--->
<cfif searchResponse IS "">
    <h1>Advanced Search</h1>
    <!---DISPLAY FORM--->
    <form name="advancedSearch" action="search.cfm" method="post">
        <input type="hidden" name="advancedSearch" />
        <input type="text" name="titleName" />
        <input type="text" name="genreID" />
        <input type="text" name="platformID" />
        <input type="button" value="Search" onclick="form.submit()" />
    </form>
</cfif>

<!---IF SEARCH IS BLANK OR LESS THAN 3 CHARACTERS DISPLAY ERROR MESSAGE--->
<cfif searchResponse IS "invalidString">
    <cfoutput>
        <h1>INVALID SEARCH</h1>
    </cfoutput>
</cfif>

<!---IF SEARCH WAS MADE BUT NO RESULTS WERE FOUND--->
<cfif searchResponse IS "noResult">
    <cfoutput>
        <h1>NO RESULT FOUND</h1>
    </cfoutput>
</cfif>

<!---IF SEARCH MADE AND RESULT WAS FOUND--->
<cfif searchResponse IS "hasResult">
    <cfoutput>
        <h1>Search Results</h1>
    </cfoutput>
    <cfoutput query="earchResult">
        <!---DISPLAY QUERY DATA--->
    </cfoutput>
</cfif>

    我的逻辑不是很有效,因为我的if语句/是否有更好的方法?以及b)您能看到我的代码可能中断的任何场景吗?我已经测试过了,但没有发现任何问题。我没有办法衡量绩效。任何想法和想法都会受到极大的赞赏。

    以下是我的功能,供参考: 

    <!---SEARCH--->
<cffunction name="searchTitles" hint="This functions searches for a title based on multiple categories" access="public" output="false">
    <cfargument name="titleName" required="no" type="string" hint="Search by title">
    <cfargument name="genreID" required="no" type="string" hint="Search by genre">
    <cfargument name="platformID" required="no" type="string" hint="Search by platform">
    <!--- DEFINE LOCAL VARIABLES - NOTE VARIABLE NAME IS QUERY NAME --->
    <cfset var searchResult = "">
    <!---GET RESULTS--->
    <cfquery name="searchResult" datasource="myDSN">
        SELECT
            games.gameID,
            games.gameReleaseDate AS rDate,
            titles.titleName AS tName,
            titles.titleShortDescription AS sDesc,
            platforms.platformName AS pName,
            genres.genreName AS gName
        FROM
            games
            INNER JOIN titles ON titles.titleID = games.titleID
            INNER JOIN platforms ON games.platformID = platforms.platformID
            INNER JOIN genres ON games.genreID = genres.genreID
        WHERE
            0=0
            <cfif ARGUMENTS.titleName IS NOT "">
                AND titles.titleName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#ARGUMENTS.titleName#%">
            </cfif>
            <cfif ARGUMENTS.genreID IS NOT "">
                AND games.genreID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.genreID#">
            </cfif>
            <cfif ARGUMENTS.platformID IS NOT "">
                AND games.platformID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.platformID#">
            </cfif>
        ORDER BY
            rDate DESC,
            tName;
    </cfquery>
    <cfreturn searchResult>
</cffunction>

    多谢

    4 回复  |  直到 15 年前
        1
  •  1
  •   Soldarnal    15 年前

    如果您在genreid和platformid参数中添加默认值“”,那么我认为您可以将顶级代码重构为: 

    <cfif StructKeyExists(url, "titleName") and Len(Trim(url.titleName)) lte 2>
    <cfset variables.searchResponse = "invalidString">
<cfelseif StructKeyExists(url, "titleName")>
    <cfinvoke component="gz.cfcomp.search" method="searchTitles" returnvariable="searchResult" argumentcollection="#url#">
    <cfset variables.searchResponse = "hasResult">
</cfif>

<cfif searchResponse eq "hasResult" and searchResult.recordCount eq 0>
    <cfset variables.searchResponse = "noResult">
</cfif>

    注意:我建议将表单方法切换为“get”进行类似的搜索,以提高用户浏览网站的体验。我已经将代码中的所有表单引用都切换到了url。

        2
  •  1
  •   James    15 年前

    你有没有想过使用全文搜索而不仅仅是标题、流派和平台之类的特定内容?全文比普通的表查询要快得多,但要花费更多的磁盘空间,并且允许对所有数据进行非常灵活的Google风格的搜索。看看下面的链接。

    http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

    如果您这样做了,您可能只需要实现对全文搜索的pass-through用户输入,因为它通过自己的语法提供所有高级搜索表达式。通过这样做,您可能不需要根据他们正在搜索的内容来分支前端逻辑,这将使解决方案更干净、更快。

        3
  •  1
  •   Dan Sorensen    15 年前

    我想这只是一种偏好,但自从谷歌出现以来,人们希望只有一个搜索框可以做任何事情。通过测试输入并有条件地运行单独的查询并返回组合的结果,可以直接从简单框中提供一些高级搜索功能。为了使结果更清晰,可以使用coldfusion或javascript突出显示匹配部分。

    我也会初始化组件,而不是多次使用cfinvoke,但我不确定这是一个需求还是一个首选项。 

    <!--- By setting a default, we don't have to test for this var's existence repeatedly --->
<cfparam name="form.simpleSearch" default="" />

<cfscript>
// Using cfscript for compactness in this little box, could use tag based cf also

// Init myComponent so that it's ready to receive searches
searcher = createObject('component','myComponent');    

// init query results
simpleResult = queryNew("id");
genres = queryNew("id");
platforms = queryNew("id");
titles = queryNew("id");

// q: shorthand 'Query' var that's trimmed and prechecked for length
q = "";
if (len(trim(form.simpleSearch)) GTE 3) {
    q = trim(form.simpleSearch);
}

// Run simple search - Should returns a query object regardless of result count
if (len(q) {
    simpleResult = searcher.simpleSearch(q);

    /* Not sure what the difference between this and simpleSearch() is unless
    this only looks for exact matches, and simpleSearch() uses LIKE and %
    wildcards for looser results. */
    titles = searcher.titleSearch(q);

    /* Conditionally run advanced searches
       - assumes that genreID and platformID are numeric
       - IF genreID and platformID are not numeric, you could use regex to test for their signature or just run them anyway knowing they wouldn't match in many cases. (BUT you must, must MUST use cfqueryparam if you attempt that)
       - Requires that advancedSearch is split into three separate functions
       - Alternately, the simpleSearch(q) or advancedSearch() could do this all internally
       - All of these functions should return an empty query if no results are found
    if (isNumeric(q)) {
        genres = searcher.genreSearch(q);
        platforms = searcher.platformSearch(q);
    }
}

</cfscript>

...

<!---ALWAYS DISPLAY SIMPLE SEARCH BAR AS IT'S PART OF THE HEADER--->
<cfoutput>    
    <form action="#cgi.script_name#" method="post">
    <input type="text" name="simpleSearch" value="#form.q#" />
    <input type="submit" value="Search" name="submit" />
</form>

<cfif titles.recordcount>
    <h3>Matching Titles</h3>
    <cfloop query="titles">
        ..html to output the titles
    </cfloop>
</cfif>

<cfif genres.recordcount>
    <h3>Matching Genres</h3>
    <cfloop query="genres">
        ..html to output the genres
    </cfloop>
</cfif>

<cfif platforms.recordcount>
    <h3>Matching Platforms</h3>
    <cfloop query="platforms">
        ..html to output the platforms
    </cfloop>
</cfif>

<cfif simpleResult.recordcount>
    <h3>Results</h3>
    <cfloop query="simpleResult">
        ..html to output the simpleResult
    </cfloop>
</cfif>

</cfouput>

    当然,有许多不同的方法可以做到这一点。这只是降低简单搜索与高级搜索复杂性的一种方法。高级结果只有在匹配的情况下才会显示,并且您可以使用mycomponent函数中的查询来调优这些结果-如果没有结果,请记住返回一个空查询。

        4
  •  1
  •   da_didi    15 年前

    我的同事告诉我,我们应该简化cfif语句:

    从“cfif something eq”开始,我们移动到“cfif not len(something)”,我们更多地使用布尔值来表示noresult和hasResult,我们将使用布尔值。

    不管怎样,其他答案都是一些很好的灵感,我需要检查我自己的搜索页面;)

    推荐文章
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号