MySQL Select 查询运行速度非常慢

Question

我有一个 MySQL 查询可以工作，但速度非常慢。我猜测是由于连接的数量。

SELECT 
    order_header.order_head_id,
    order_header.order_date,
    order_header.status,
    suppliers.supplier,
    categories.category,
    order_header.user,
    order_header.sage_ref,
    SUM(order_lines.total_price) AS price
FROM
    order_header
        LEFT JOIN
    order_lines ON order_header.order_head_id = order_lines.order_head_id
        LEFT JOIN
    suppliers ON order_header.supplier_id = suppliers.supp_id
        LEFT JOIN
    categories ON order_header.category = categories.cat_id
WHERE
    order_header.status LIKE '%'
        AND order_header.order_head_id LIKE '%'
        AND order_header.user LIKE '%'
GROUP BY order_header.order_head_id
ORDER BY order_head_id DESC
LIMIT 50;

EXPLAIN 查询的结果

显示创建表结果

CREATE TABLE `categories` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=latin1


CREATE TABLE `order_header` (
  `order_head_id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(45) DEFAULT NULL,
  `category` varchar(45) NOT NULL,
  `order_date` date DEFAULT NULL,
  `supplier_id` varchar(45) NOT NULL,
  `user` varchar(45) DEFAULT NULL,
  `sage_ref` varchar(45) DEFAULT NULL,
  `query_notes` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`order_head_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2249 DEFAULT CHARSET=latin1

CREATE TABLE `order_lines` (
  `order_lines_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_head_id` int(11) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `unit_price` decimal(65,2) DEFAULT NULL,
  `total_price` decimal(65,2) DEFAULT NULL,
  PRIMARY KEY (`order_lines_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3981 DEFAULT CHARSET=latin1

CREATE TABLE `suppliers` (
  `supp_id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier` varchar(255) DEFAULT NULL,
  `status` varchar(225) DEFAULT NULL,
  PRIMARY KEY (`supp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=161 DEFAULT CHARSET=latin1

SQL 版本 5.6.30

我不太擅长 MySQL，想知道是否有人可以找到一种方法来改进查询，使其运行得更快。

我们将非常感谢您的帮助。

非常感谢，

约翰

Answer 1

将第一个(左)连接包装到 GROUP BY 子查询中是有意义的。 GROUP BY 和 LIMIT 将限制以下两个连接中使用的行数:

SELECT 
    x.order_head_id,
    x.order_date,
    x.status,
    suppliers.supplier,
    categories.category,
    x.user,
    x.sage_ref,
    x.price
FROM (
    SELECT 
      order_header.supplier_id,
      order_header.category,
      order_header.order_head_id,
      order_header.order_date,
      order_header.status,
      order_header.user,
      order_header.sage_ref,
      SUM(order_lines.total_price) AS price
    FROM order_header
    LEFT JOIN order_lines ON order_header.order_head_id = order_lines.order_head_id
    WHERE order_header.status LIKE '%'
      AND order_header.order_head_id LIKE '%'
      AND order_header.user LIKE '%'
    GROUP BY order_header.order_head_id
    ORDER BY order_head_id DESC
    LIMIT 50
) x
LEFT JOIN suppliers  ON x.supplier_id = suppliers.supp_id
LEFT JOIN categories ON x.category    = categories.cat_id
ORDER BY order_head_id DESC