时间戳

首页 > 解决方案 > 需要帮助重写缓慢的 mysql 查询

mysql - 需要帮助重写缓慢的 mysql 查询

问题描述

我有 2 个缓慢的 MySQL 查询：

SELECT count(N) as c, mW as move, move.fenW as fen FROM move,opening,book  WHERE move.fenW LIKE BINARY ‘S’  AND move.oid=opening.id AND opening.bid=book.bid AND book.is_deleted=N AND opening.deleted=N AND mW!=‘S’ AND book.published=N GROUP BY mW ORDER BY c DESC

和

SELECT count(N) as c, mB as move, move.fenB as fen FROM move,opening,book  WHERE move.fenB LIKE BINARY ‘S’  AND move.oid=opening.id AND opening.bid=book.bid AND book.is_deleted=N AND opening.deleted=N AND mB!=‘S’ AND book.published=N GROUP BY mB ORDER BY c DESC

这些查询导致网站速度加载显着延迟。我正在寻找有关如何改进/重写这些查询以提高网站加载速度和性能的建议。

标签： mysql

解决方案

您的 like 运算符可能会减慢此查询的速度。让我们先过滤您的表，然后再加入其他表。

SELECT count(N) as c
    , m.mB as move
    , m.fenB as fen 
FROM 
    (SELECT mB, fenB, oid FROM move WHERE m.fenB LIKE BINARY ‘S’ AND mB!=‘S’) m
INNER JOIN opening o on m.oid = o.id AND o.deleted=N    
INNER JOIN book  b on o.bid=b.bid AND b.is_deleted=N  AND b.published=N 
GROUP BY m.mB 
ORDER BY c DESC

推荐阅读