mysql - 如何加速复杂的 MYSQL 查询？

Question

我在从 ColdFusion 网站调用的 Cold Fusion 组件中有以下 MYSQL 查询。我有一个自定义 take，它基本上为以下字段构建 bool 查询:“PlaceName,Country,Adm1,adm2,adm3,locality”。

<!--- SEARCH PLACES 2--->
<cffunction name="searchPlaces_full" access="public" returntype="query">
    <cfargument name="q" type="string" required="yes">


    <CF_BOOLSEARCH searchterm="#arguments.q#" field="PlaceName,Country,Adm1,adm2,adm3,locality " booloperator="and">
    <cfquery name="GetPlaces" datasource="#application.settings.dsn#">
    SELECT 
        places.CatID,
        places.PlaceName,
        places.PlaceID,
        places.Address1,
        places.PostalCode,
        places.Locality,
        places.Address2,
        places.ImageThumb,
        places.Adm1,
        places.Country,
        places.Adm2,
        places.Adm3,
        places.contributorid,
        places.lng,
        places.lat,
        places.verified,
        places.verified_by,
        places.verified_date
    FROM places INNER JOIN places_cats ON places.PlaceID = places_cats.PlaceID
    WHERE  
    <cfif len(trim(arguments.q))>
    (#PreserveSingleQuotes(boolsearch)#)
    <cfelse>
    1=0
    </cfif>
    AND places_cats.CATID IN (#arguments.categories#)
    GROUP BY 
        places.CatID,
        places.PlaceName,
        places.PlaceID,
        places.Address1,
        places.PostalCode,
        places.Locality,
        places.Address2,
        places.ImageThumb,
        places.Adm1,
        places.Country,
        places.Adm2,
        places.Adm3,
        places.contributorid,
        places.lng,
        places.lat,
        places.verified,
        places.verified_by,
        places.verified_date
        ORDER BY PlaceName
        </cfquery>


    <cfreturn getPlaces>
</cffunction>

数据库中有 624227 条记录。如果我搜索 Chappaqua，实际运行的 SQL 如下:

       SELECT 
        places.CatID,
        places.PlaceName,
        places.PlaceID,
        places.Address1,
        places.PostalCode,
        places.Locality,
        places.Address2,
        places.ImageThumb,
        places.Adm1,
        places.Country,
        places.Adm2,
        places.Adm3,
        places.contributorid,
        places.lng,
        places.lat,
        places.verified,
        places.verified_by,
        places.verified_date
    FROM places INNER JOIN places_cats ON places.PlaceID = places_cats.PlaceID
    WHERE  

    (((PlaceName LIKE '%chappaqua%') OR (Country LIKE '%chappaqua%') OR (Adm1 LIKE '%chappaqua%') OR (adm2 LIKE '%chappaqua%') OR (adm3 LIKE '%chappaqua%') OR (locality  LIKE '%chappaqua%')))

    AND places_cats.CATID IN (1,21,15,32,16,26,29,27,28,25,75,89,38,5,36,88,87,31,33,24,35,37,90,39,40,34,30,9,8,7,11,20,19,96,97,95,13,17,14,12,3,2,4,84,85,86)
    GROUP BY 
        places.CatID,
        places.PlaceName,
        places.PlaceID,
        places.Address1,
        places.PostalCode,
        places.Locality,
        places.Address2,
        places.ImageThumb,
        places.Adm1,
        places.Country,
        places.Adm2,
        places.Adm3,
        places.contributorid,
        places.lng,
        places.lat,
        places.verified,
        places.verified_by,
        places.verified_date
        ORDER BY PlaceName

我知道它丑陋而复杂。运行大约需要 1836ms。有没有更好的方法来编写查询或代码，以便在一秒钟内加快返回数据的速度？

这是对 SQL 的解释:

Answer 1

问题是引擎正在对表进行全表扫描，然后根据分组依据对结果进行排序。

由于 like 的缘故，全表扫描似乎是必要的:

(((PlaceName LIKE '%chappaqua%') OR (Country LIKE '%chappaqua%') OR (Adm1 LIKE '%chappaqua%') OR (adm2 LIKE '%chappaqua%') OR (adm3 LIKE '%chappaqua%') OR (locality  LIKE '%chappaqua%')))

问题是无法使用 PlaceName 上的索引，因为初始字符不固定。

所以。 . .您可以删除 group by 吗？您至少可以将其替换为 distinct，但我认为这不会影响查询计划。你有 place_cats(placeId, catId) 的索引吗？这至少会阻止查询读取类别表，因此它可以只进行索引查找。

您能否将搜索限制为仅搜索字段开头的单词？

我能想到的唯一其他选择是在 MySQL 中切换到使用全文索引。