SQL查询包含许多连接的大量数据(SQL query for large amount of data with many joins)

SQL查询包含许多连接的大量数据(SQL query for large amount of data with many joins)

我已经为我的要求编写了一个SQL查询。 这对我来说很好。 这需要0.0006秒来执行。

我想从sql专家那里知道“这对大量数据有效吗？”。

我在下面写了我的查询。

SELECT HM_customers.id, HM_customers.username, HM_customers.firstname, HM_customers.lastname, HM_customers.company, HM_customers_address_bank.field_data FROM HM_orders JOIN HM_order_items ON HM_order_items.order_id = HM_orders.id JOIN HM_bid ON HM_order_items.bid_id = HM_bid.bid_id JOIN HM_customers ON HM_bid.user_id = HM_customers.id JOIN HM_customers_address_bank ON HM_customers_address_bank.id = HM_customers.default_billing_address WHERE HM_orders.id = '4'

任何专家都可以建议我或让我知道如何改进此查询。 如果此查询中有任何问题，请建议我。

注意： - 这是一个简单的查询。 但是我想知道，这将使用更少的时间来处理大量数据

I have written a sql query for my requirement. This is working fine for me. This is taking 0.0006 sec to execute.

I want to know from sql experts "will this work fine with large amount of data?".

I have written my query below.

SELECT HM_customers.id, HM_customers.username, HM_customers.firstname, HM_customers.lastname, HM_customers.company, HM_customers_address_bank.field_data FROM HM_orders JOIN HM_order_items ON HM_order_items.order_id = HM_orders.id JOIN HM_bid ON HM_order_items.bid_id = HM_bid.bid_id JOIN HM_customers ON HM_bid.user_id = HM_customers.id JOIN HM_customers_address_bank ON HM_customers_address_bank.id = HM_customers.default_billing_address WHERE HM_orders.id = '4'

Any expert can advice me or let me know how can I improve this query. Please suggest me if any issue in this query.

NOTE:- This is a simple query. But I want to know, will this work with large amount of data with less time

最满意答案

您不需要包含orders表：

SELECT c.id, c.username, c.firstname, c.lastname, c.company, cb.field_data FROM HM_order_items oi JOIN HM_bid b ON oi.bid_id = b.bid_id JOIN HM_customers c ON b.user_id = c.id JOIN HM_customers_address_bank cb ON cb.id = c.default_billing_address WHERE oi.order_id = '4';

如果客户多次对相同的商品出价，您的查询也会导致重复的行。 如果你输入一个select distinct ，那么你将产生重复消除的开销。 如果这成为问题，您可能希望将查询重构为exists 。

You don't need to include the orders table:

SELECT c.id, c.username, c.firstname, c.lastname, c.company, cb.field_data FROM HM_order_items oi JOIN HM_bid b ON oi.bid_id = b.bid_id JOIN HM_customers c ON b.user_id = c.id JOIN HM_customers_address_bank cb ON cb.id = c.default_billing_address WHERE oi.order_id = '4';

Your query can also result in duplicate rows, if a customer bids on the same items multiple times. If you put in a select distinct, then you will incur overhead of duplicate elimination. If this becomes a problem, you will probably want to restructure the query as an exists.