Mysql查询耗时
Qe : 1)
select
a.finishproductid
from
tblt_invoiceorderitems a, tblm_invoiceorder b
where
b.invoiceorderdate <= 'Current Date' and
a.invoiceorderid = b.invoiceorderid
--- 它有超过 16k 条记录。
Qe : 2 )
select jobcardid, stockcode
from tblm_finishproduct
where productionentrydate <= 'Current Date'
--- 它也有超过 16k 条记录。
现在我想要来自第二个查询而不是第一个查询。
select jobcardid, stockcode
from
tblm_finishproduct
where
productionentrydate <= 'CurrrntDate' and
finishproductid not in
(
select
a.finishproductid
from
tblt_invoiceorderitems a, tblm_invoiceorder b
where
b.invoiceorderdate <= 'CurrrntDate' and
a.invoiceorderid = b.invoiceorderid
);
现在需要一些时间
Qe : 1)
select
a.finishproductid
from
tblt_invoiceorderitems a, tblm_invoiceorder b
where
b.invoiceorderdate <= 'Current Date' and
a.invoiceorderid = b.invoiceorderid
--- it has more than 16k records.
Qe : 2 )
select jobcardid, stockcode
from tblm_finishproduct
where productionentrydate <= 'Current Date'
--- it has also more than 16k Record.
Now i Want From 2nd Query not in first query.
select jobcardid, stockcode
from
tblm_finishproduct
where
productionentrydate <= 'CurrrntDate' and
finishproductid not in
(
select
a.finishproductid
from
tblt_invoiceorderitems a, tblm_invoiceorder b
where
b.invoiceorderdate <= 'CurrrntDate' and
a.invoiceorderid = b.invoiceorderid
);
Now its taking a time
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这并不能解决您的问题，但您不应该在 where 子句中使用
a.invoiceorderid = b.invoiceorderid- 尝试用此查询代替第一个查询。 自然连接比进行叉积更有效整个表并仅选择那些匹配的行。This does not address your question, but you should not be using
a.invoiceorderid = b.invoiceorderidin your where clause--try this query in place of the first one. Natural join is more efficient than making a cross product of the entire table and selecting only those rows that match.将其从 not in 中删除：
Remove this from the not in:
“select Table1, Table2, Table3”的结果可能是 16k^3 或 16k^2。您需要使用主键进行“内部联接”或“联接”来计算搜索。
再见。
the result of "select Table1, Table2, Table3" maby is 16k^3 or 16k^2. You need make a "inner join" or "join" using primary key to cout the search.
See u.