优化同一表上的不在子句中的SQL查询

发布于 2025-02-04 21:12:55 字数 1526 浏览 3 评论 0原文

我希望能够从订单表中选择所有条目，这些条目在2019年之前已订购了某个产品，但不在之后。该桌子有接近7m的条目，以下查询似乎需要近4分钟才能运行。请注意，在订单表中，productid是产品表的外键，并索引。我们可以将以下查询重写以更优化和更好的性能时间吗？任何帮助将不胜感激。谢谢，

SELECT distinct *
FROM orders o
WHERE o.year < '2019'
AND o.productid NOT IN (
                        SELECT distinct(productid)
                        FROM orders
                        WHERE year > '2019');

请在下面找到Divell Commmand的输出

+----+--------------------+-------+------------+------+------------------------+------------------------+---------+--------------------------+---------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys          | key                    | key_len | ref                      | rows    | filtered | Extra       |
+----+--------------------+-------+------------+------+------------------------+------------------------+---------+--------------------------+---------+----------+-------------+
|  1 | PRIMARY            | o     | NULL       | ALL  | NULL                   | NULL                   | NULL    | NULL                     | 2124177 |    33.33 | Using where |
|  2 | DEPENDENT SUBQUERY | o2    | NULL       | ref  | FK_orders_product | FK_orders_product | 4       | test-db.o.productid |       3 |    33.33 | Using where |
+----+--------------------+-------+------------+------+------------------------+------------------------+---------+--------------------------+---------+----------+-------------+
2 rows in set, 2 warnings (0.05 sec)

原文

I would like to be able to select all entries from the orders table where a certain product has been ordered prior to 2019 but not after it. The table has close to 7M entries and the below query seems to take almost ~4 minutes to run. Note that in the orders table productId is a foreign key to products table and is indexed. Could we rewrite the below query to be more optimized and better in performance time ? Any help is greatly appreciated. Thank you

SELECT distinct *
FROM orders o
WHERE o.year < '2019'
AND o.productid NOT IN (
                        SELECT distinct(productid)
                        FROM orders
                        WHERE year > '2019');

Please find below the output from explain commmand

+----+--------------------+-------+------------+------+------------------------+------------------------+---------+--------------------------+---------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys          | key                    | key_len | ref                      | rows    | filtered | Extra       |
+----+--------------------+-------+------------+------+------------------------+------------------------+---------+--------------------------+---------+----------+-------------+
|  1 | PRIMARY            | o     | NULL       | ALL  | NULL                   | NULL                   | NULL    | NULL                     | 2124177 |    33.33 | Using where |
|  2 | DEPENDENT SUBQUERY | o2    | NULL       | ref  | FK_orders_product | FK_orders_product | 4       | test-db.o.productid |       3 |    33.33 | Using where |
+----+--------------------+-------+------------+------+------------------------+------------------------+---------+--------------------------+---------+----------+-------------+
2 rows in set, 2 warnings (0.05 sec)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

涙—继续流 2025-02-11 21:12:55

您可以使用不存在。

希望Year列不是VARCHAR，因此您不应使用字符串文字。大概使用选择 *意味着不会有任何重复项，因此您应该删除 dinters 。

您的一年范围也完全排除了2019年，所以大概您的谓词之一应该等于2019年？

select *
from orders o
where o.year < 2019
  and not exists (
    select *
    from orders o2
    where o2.productid = o.productid
      and Year >= 2019
  );

You could use not exists.

Hopefully the year column is not a varchar so you should not be using string literals. Presumably using select * means there won't be any duplicates so you should remove distinct.

Your year ranges also exclude 2019 completely, so presumably one of your predicates should be equal to 2019?

select *
from orders o
where o.year < 2019
  and not exists (
    select *
    from orders o2
    where o2.productid = o.productid
      and Year >= 2019
  );

回复收藏 0 原文