MySql性能问题

Question

我的这个查询花了45秒才能执行。我对正在搜索的所有字段都有索引。

SELECT SQL_CALC_FOUND_ROWS g.app_group_id, g.id as g_id, p.`id` as form_id, 
       a.`user`   as activity_user,a.`activity` as app_act ,a.* 
FROM grouped g 
INNER JOIN 
(SELECT max(id) as id, app_group_id FROM grouped GROUP BY app_group_id) g1 
ON g1.app_group_id = g.app_group_id AND g.id = g1.id 
INNER JOIN form p 
on p.id = g.id 
    INNER JOIN 
    (SELECT a.id, a.date_time, a.user, a.activity FROM log a) a 
    ON g.id = a.id 
WHERE p.agname like '%blahblah%' and p.`save4later` != 'y' 
      and a.activity = 'APP       Submitted' or a.activity = 'InstaQUOTE' 
ORDER BY app_group_id DESC limit 0, 100

在我的解释中，它显示了im使用临时；使用filesort

索引包括：

活动表:主activity_id索引date_time索引id索引活动索引user

表格:主id索引id_md5索引dateadd索引dateu索引agent_or_underwriter索引

分组表:唯一id索引app_group_id索引agent_or_underwriter save4later

任何建议都是非常感谢的

非常感谢

Answer 1

首先，试试这个：

SELECT
  g.app_group_id,
  g.id AS g_id,
  p.id AS form_id,
  a.user AS activity_user,
  a.activity AS app_act,
  a.id,
  a.date_time
FROM grouped g 
INNER JOIN 
  (SELECT MAX(id) AS id, app_group_id FROM grouped GROUP BY app_group_id) g1 
  ON g1.app_group_id = g.app_group_id AND g.id = g1.id 
INNER JOIN form p 
  ON p.id = g.id 
INNER JOIN log a 
  ON g.id = a.id 
WHERE p.agname LIKE '%blahblah%'
  AND p.save4later != 'y'
  AND a.activity IN('APP       Submitted', 'InstaQUOTE')
LIMIT 0, 100

我删除了一个不必要的子查询。还删除了ORDER BY。我想你可以不用排序，这一定会大大提高查询速度。

我还删除了SQL_CALC_FOUND_ROWS，因为正如我前面提到的，发出单独的COUNT(*)查询应该会更快。

Answer 2

您的where子句在"a.Activity“上有一个"Or”。如果没有围绕这两个活动的()，它将遍历所有内容，所有P、G、G1别名。我猜这可能是你更大的问题。

此外，我将确保您的“表单”表上有一个索引，其中包含索引( Save4Later )列

我会像这样更新查询：

SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS 
      g.app_group_id, 
      g.id as g_id, 
      QualifyPages.Form_id, 
      a.`user` as activity_user,
      a.`activity` as app_act,
      a.* 
   FROM
      ( select p.ID as Form_ID
           from  FORM p
           WHERE p.`save4later` != 'y' 
             AND p.agname like '%blahblah%' ) QualifyPages

         JOIN Grouped g
            on QualifyPages.Form_ID = g.ID

            INNER JOIN 
            ( SELECT app_group_id,
                     max(id) as MaxIDPerGroup
                 FROM 
                    grouped 
                 GROUP BY 
                    app_group_id ) g1 
             ON g.app_group_id = g1.app_group_id
            AND g.id = g1.MaxIDPerGroup 

           INNER JOIN 
            ( SELECT a.id, a.date_time, a.user, a.activity 
                 FROM log a
                 WHERE a.activity = 'APP       Submitted' 
                    or a.activity = 'InstaQUOTE' ) a 
               ON g.id = a.id 
   ORDER BY 
      g.app_group_id DESC 
   limit 
      0, 100