SQL查询优化建议产品 [英] SQL Query Optimization Suggested Products

问题描述

我现在将尝试更好地解释以下问题和查询的目的. 假设我们正在谈论电子商务环境和数据库.除其他外，我们还有三个表:products，orders和orders_data. ORDERS表将处理所有下达的订单和一个子表，我们将其称为ORDERS_DATA将存储订单中记录的所有产品.

I will try now to better explain what is the purpose of the following question and query. Let's assume we are talking about a ecommerce environment and database. We have, among many others, three tables: products, orders and orders_data. The ORDERS table will handle all placed orders and a sub-table, which we will call ORDERS_DATA will store all products recorded within an order.

遵循表的定义，对于我的问题，没有那些无用的字段:

Following the tables definition, without those unuseful fields for my question:

    ORDERS (*id*, date, totale, ...);
    ORDERS_DATA (id_order, id_product, ...);
    PRODUCTS (id, name, ...);

ORDERS 中的主键为 id ，而在ORDERS_DATA中，主键为(id_order，id_product).

The primary key in ORDERS is id, while in ORDERS_DATA the key is (id_order, id_product).

在给定 PRODUCT ID (也同时在产品页面或购物车页面上浏览)的情况下，我想对查询进行的查询是基于ORDERS_DATA表的推荐产品.该查询将返回存在于这些订单中的所有id_product，其中至少有一个产品是给定的 PRODUCT ID

What I would like to do with a query is to retrive, given a PRODUCT ID (while surfing on a product page or cart page as well), suggested products based on ORDERS_DATA table. The query will return all id_product which lives in those orders, where, at least one of these products is the given PRODUCT ID

为了简单起见，我将举一个例子.假设我们在ORDERS_DATA中有这些ROW:

For the sake of semplicity I will report an example. Let's assume we have these ROWs in ORDERS_DATA:

R1(1, 1); R2(1, 2); R3(1, 3); R4(2, 2), R5(2, 5); R6(3, 3);

我想要产品ID = 2的建议.我的查询将返回ID:1和3(来自R1和R3-它们共享相同的订单ID，并且此订单也具有产品2-来自R2-顺其产品)和产品5归功于订单号2.订单号3将被忽略.

I want suggestions for product id = 2. My query will return ids: 1 and 3 (from R1 and R3 - they share the same order id and this order have also product 2 - from R2 - btw its products) and product 5 thanks to the order number 2. Order number 3 will be ignored.

这是我写的查询，但是我很确定这不是性能和风格上最好的查询.

This is the query I wrote, but I'm quite sure it is not the best one in performance and style.

SELECT 
    A.id_product, COUNT( A.id_product ) AS num
FROM  
    orders_data A, orders_data B
WHERE 
    A.id_order = B.id_order
    AND B.id_product IN  (*IDs-HERE*)
    AND A.id_product NOT IN (*IDs-HERE*)
GROUP BY A.id_product

我已经使用了INNER JOIN语法，但是性能没有任何变化. 该查询仅在IN Clausole中使用一个产品ID花费0,0022sec.使用多个产品ID(例如，在购物车页面期间，在购物篮中有更多产品)，性能将呈指数级下降.

I've already use the INNER JOIN syntax but nothing change in performance. The query take 0,0022sec with just one product id in the IN clausole. Performance will decrese exponetially with multiple products id (for instance during the cart page, with more products in the basket).

谢谢.

推荐答案

尝试将JOIN替换为EXISTS测试:

SELECT
   id_product,
   COUNT(1) As num
FROM
   orders_data As A
WHERE
   id_product NOT IN (*IDs HERE*)
And
   Exists
   (
      SELECT 1
      FROM orders_data As B
      WHERE B.id_order = A.id_order
      And B.id_product IN (*IDs HERE*)
   )
GROUP BY
   id_product

这篇关于SQL查询优化建议产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！