Mysql测试两个表中的任何一个是父母和子女的值

Question

MySQL的问题。

我有合同和订单文件。一个标准的父母和孩子 - 一对多的关系。 合同可以有很多订单。公共连接字段是c_contract_id = co_contract_id。

合同有一个c_type_code来限定合同并且订单也有一个co_order_type_code。

我正在构建一个通用搜索，允许用户根据很多字段进行选择。 给我带来麻烦的查询是在合同或contract_orders表中找到具有特定type_code的合同的contract_id。

用户可以搜索的表格中有许多字段。我的问题的相关领域是：

CREATE TABLE `ndx_contracts` (
    `c_contract_id` int(11) NOT NULL AUTO_INCREMENT, 
    `c_type_code` char(2) NOT NULL, 
    PRIMARY KEY (`c_contract_id`), 
    KEY `c_type_code` (`c_type_code`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 


CREATE TABLE `ndx_contract_orders` (
    `co_contract_id` int(11) NOT NULL, 
    `co_contract_orderid` int(11) NOT NULL AUTO_INCREMENT, 
    `co_order_type_code` varchar(2) NOT NULL, 
    `co_data` varchar(255), 
    PRIMARY KEY (`co_contract_orderid`,`co_contractid`), 
    KEY `co_order_type_code` (`co_order_type_code`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 


SELECT count(1) 
FROM contracts 
WHERE 
    c_type_code IN ('02') 
     OR (
       (SELECT count(co_order_type_code) FROM contract_orders 
        WHERE 
         co_contract_id = c_contract_id 
         AND co_order_type_code IN ('02') 
       ) > 0 
      ) 
;  

此查询工作，但它是非常缓慢。我只有约40,000份合同，每份约有4份订单记录，并且搜索需要311秒才能返回320秒行。

我会做一个外连接，但那会返回很多额外的行。

谢谢

Answer 1

这听起来像你想算不同的合同ID，所以这应该为你做它。

SELECT COUNT(DISTINCT c.c_contract_id) AS `contract_id_count` 
FROM ndx_contracts AS c 
LEFT OUTER JOIN ndx_contract_orders AS co 
    ON c.c_contract_id = co.co_contract_id 
WHERE c.c_type_code = ? OR co.co_order_type_code = ? 

注意这将允许没有订单的合同情况。显然，?将会替换为您的搜索值。如果你想要实际的订单ID本身，只需删除选择中的COUNT()函数。

您在ndx_contract_orders上的主键实际上是一个问题。为了做到这一点，你只需要通过contract_id查找，你需要颠倒该主索引的字段顺序，只能通过contract_id启用索引查找。或者，您可以为co_contract_id添加一个附加索引。老实说，如果订单ID是一个自动增量，这个领域本身应该可能是您的主要关键，并且co_contract_id字段应该只有它自己的索引。因为order_id上的主键自动增量将确保唯一性，所以不需要在两个字段中强制使用唯一索引。