我有一个类似于以下内容的Postgres(9.1)客户数据库:
customers.id
customers.lastname
customers.firstname
invoices.id
invoices.customerid
invoices.total
invoicelines.id
invoicelines.invoiceid
invoicelines.itemcode
invoicelines.price
我建立了一个搜索,列出了所有购买过某个项目(比如"abc")的客户。
Select * from customers WHERE customers.id IN
(Select invoices.customerid FROM invoices WHERE invoices.id IN
(Select invoicelines.invoiceid FROM invoicelines WHERE
invoicelines.itemcode = 'abc')
)
搜索工作正常,并显示正确的客户,但在包含 200 万张发票和 200 万个行项目的数据库中大约需要 10 秒左右。
我想知道是否有另一种方法可以稍微减少这一点。
另一种方法是使用 EXISTS
:
Select *
from customers
WHERE EXISTS (
Select invoices.customerid
FROM invoices
JOIN invoicelines
ON invoicelines.invoiceid = invoices.id AND
invoicelines.itemcode = 'abc' AND
customers.id = invoices.customerid)
改用exists
。 我怀疑这可能效果很好:
Select c.*
from customers c
where exists (Select 1
from invoices i join
invoicelines il
on i.id = il.invoiceid and il.itemcode = 'abc'
where c.id = i.customerid
);
为此,您需要确保拥有正确的索引:invoices(customerid, id)
和invoicelines(invoiceid, itemcode)
。
您是否希望该客户商品的itemcode
customer
中的所有行和列都'abc
'?如果您在customerid
加入,则可以找到这些项目的所有客户信息。如果该列表中有重复项,则可以使用DISTINCT
,每个customerID
只会给您一个条目。
SELECT
DISTINCT [List of customer columns]
FROM
customers
INNER JOIN
invoicelines
ON
customers.customerid = invoicelines.customerid
AND
invoicelines.itemcode = 'abc'