比 NOT IN 更高效的查询(嵌套选择) [英] More efficient query than NOT IN (nested select)

问题描述

我有两个表 table1 和 table2 它们的定义是:

I have two tables table1 and table2 their definitions are:

CREATE `table1` (
    'table1_id' int(11) NOT NULL AUTO_INCREMENT,
    'table1_name' VARCHAR(256),
     PRIMARY KEY ('table1_id')
)

CREATE `table2` (
    'table2_id' int(11) NOT NULL AUTO_INCREMENT,
    'table1_id' int(11) NOT NULL,
    'table1_name' VARCHAR(256),
     PRIMARY KEY ('table2_id'),
     FOREIGN KEY ('table1_id') REFERENCES 'table1' ('table1_id')
)

我想知道 table1 中没有在 table2 中引用的行数，可以用:

I want to know the number of rows in table1 that are NOT referenced in table2, that can be done with:

SELECT COUNT(t1.table1_id) FROM table1 t1 
WHERE t1.table1_id NOT IN (SELECT t2.table1_id FROM table2 t2)

是否有更有效的方式来执行此查询?

Is there a more efficient way of performing this query?

推荐答案

升级到 MySQL 5.6，更好地针对子查询优化半连接.

Upgrade to MySQL 5.6, which optimizes semi-joins against subqueries better.

见http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

或者使用排除连接:

SELECT COUNT(t1.table1_id) FROM table1 t1 
LEFT OUTER JOIN table2 t2 USING (table1_id)
WHERE t2.table1_id IS NULL

另外，确保 table2.table1_id 上有索引.

Also, make sure table2.table1_id has an index on it.

这篇关于比 NOT IN 更高效的查询(嵌套选择)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！