mysql - 计算字段中的 Have 查询速度慢

Question

我的查询速度很慢...我想显示我附近(靠近登录用户)的最后 12 个最新成员，并且我的开发数据库有 15 万行。

花了 1 秒多的时间，解释查询告诉我过滤了 30k 行因此，在我的开发数据库中过滤了 30k 行以获取 150k 行...我的在线服务器比这个大得多...

这是我的查询:

SELECT  profils.*,
        Users.username,
        ( SELECT  count(*)
                from  profilsphotos pp
            where  pp.iduser=Profils.iduser
        ) as nbpics,
        ATAN2(SQRT(POW(COS(RADIANS(50.78961000)) * SIN(RADIANS(Y(gm_coor) - 4.64956000)),
                        2) + POW(COS(RADIANS(X(gm_coor))) * SIN(RADIANS(50.78961000)) - SIN(RADIANS(X(gm_coor))) * COS(RADIANS(50.78961000)) * COS(RADIANS(Y(gm_coor) - 4.64956000)),
                                                2)), (SIN(RADIANS(X(gm_coor))) * SIN(RADIANS(50.78961000)) + COS(RADIANS(X(gm_coor))) * COS(RADIANS(50.78961000)) * COS(RADIANS(Y(gm_coor) - 4.64956000)))
             ) * 6372.795 AS distance
    from  Users
    inner join  Profils  ON Users.id=Profils.iduser
    where  Profils.Actif=1
      and  profils.idsexe=2
      and  profils.idlookingfor=1
      and  Profils.iduser<>1
    HAVING  distance<400
    order by  Users.id desc, distance asc
    limit  12 

请注意，我在这四个字段上添加了索引:actif、idsexe、idlookingfor 和 iduser

我的查询有什么问题？

非常感谢!

帕斯卡

Answer 1

我会将 SELECT 子句中的子查询提取到临时表中，对其进行索引并连接到它，而不是对 select 子句中的每条记录执行它(30K 次)。

所以步骤是:创建一个临时表，为其建立索引，运行优化的查询。

首先，为查询创建相关索引:

ALTER TABLE
  `Profils`
ADD
  INDEX `profils_idx_actif_iduser` (`Actif`, `iduser`);

ALTER TABLE
  `Users`
ADD
  INDEX `users_idx_id_username` (`id`, `username`);

ALTER TABLE
  `profils`
ADD
  INDEX `profils_idx_idsexe_idlookingfor` (`idsexe`, `idlookingfor`);

ALTER TABLE
  `profilsphotos`
ADD
  INDEX `profilsphotos_idx_iduser` (`iduser`);

现在，创建临时表并为其建立索引:

-- Transformed subquery to a temp table to improve performance
CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS SELECT
        count(*) AS nbpics,
        iduser 
    FROM
        profilsphotos pp 
    WHERE
        1 = 1 
    GROUP BY
        iduser 
    ORDER BY
        NULL;

ALTER TABLE
  `temp1`
ADD
  INDEX `temp1_idx_iduser_nbpics` (`iduser`, `nbpics`);

现在尝试运行此查询而不是原始查询，看看它是否运行得更快:

SELECT
        optimizedSub1.*,
        temp1.nbpics 
    FROM
        (SELECT
            Users.username,
            ATAN2(SQRT(POW(COS(RADIANS(50.78961000)) * SIN(RADIANS(Y(Profils.gm_coor) - 4.64956000)),
            2) + POW(COS(RADIANS(X(Profils.gm_coor))) * SIN(RADIANS(50.78961000)) - SIN(RADIANS(X(Profils.gm_coor))) * COS(RADIANS(50.78961000)) * COS(RADIANS(Y(Profils.gm_coor) - 4.64956000)),
            2)),
            (SIN(RADIANS(X(Profils.gm_coor))) * SIN(RADIANS(50.78961000)) + COS(RADIANS(X(Profils.gm_coor))) * COS(RADIANS(50.78961000)) * COS(RADIANS(Y(Profils.gm_coor) - 4.64956000)))) * 6372.795 AS distance 
        FROM
            Users 
        INNER JOIN
            Profils 
                ON Users.id = Profils.iduser 
        WHERE
            Profils.Actif = 1 
            AND profils.idsexe = 2 
            AND profils.idlookingfor = 1 
            AND Profils.iduser <> 1 
        HAVING
            distance < 400 
        ORDER BY
            Users.id DESC,
            distance ASC LIMIT 12) AS optimizedSub1 
    LEFT JOIN
        temp1 
            ON temp1.iduser = optimizedSub1.iduser