我已经为我的要求编写了一个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.
更多推荐
发布评论