SQL内部连接:数据库卡住 [英] SQL Inner Join : DB stuck
问题描述
几天前,我张贴了这个问题,但我没有确切说明我想要的. 我再次提出更好的问题: 为了澄清我的问题,我添加了一些新信息:
I postet this question a few days ago but I didn't explain exactly what I want. I ask the question better formulated again: To clarify my problem I added some new information:
我有一个带有MyISAM表的MySQL数据库.两个相关的表是:
I got an MySQL DB with MyISAM tables. The two relevant tables are:
* orders_products: orders_products_id, orders_id, product_id, product_name, product_price, product_name, product_model, final_price, ...
* products: products_id, manufacturers_id, ...
(有关表格的完整信息,请参见屏幕截图产品(屏幕截图)和截图screenshot_products(截图))
(for full information about the tables see screenshot products (Screenshot) and screenshot orders_products (Screenshot))
现在我想要的是:-获取所有订购了Manufacturers_id = 1的产品的订单.此订单产品的产品名称(Manufacturers_id = 1).按订单分组.
Now what I want is this: - Get all Orders who ordered products with manufacturers_id = 1. And the product name of the product of this order (with manufacturers_id = 1). Grouped by orders.
到目前为止,我所做的是:
What I did so far is this:
SELECT
op.orders_id,
p.products_id,
op.products_name,
op.products_price,
op.products_quantity
FROM orders_products op , products p
INNER JOIN products
ON op.products_id = p.products_id
WHERE p.manufacturers_id = 1 AND
p.orders_id > 10000
p.orders_id> 10000以进行测试以仅获取几个order_id.但是,即使查询有效,它们的查询也需要花费大量时间才能执行.两次sql服务器卡住了.错误在哪里?
p.orders_id > 10000 for testing to get only a few order_id's. But thies query takes much time to get executed if it even works. Two times the sql server stucked. Where is the mistake?
推荐答案
SELECT
op.orders_id,
p.products_id,
op.products_name,
op.products_price,
op.products_quantity
FROM orders_products op
INNER JOIN products p
ON op.products_id = p.products_id
WHERE p.manufacturers_id = 1 AND
p.orders_id > 10000
您在product表上同时具有隐式和显式连接(请注意不要再使用隐式连接语法,这是非常不好的programmin做法),然后看一下代码,我怀疑您正在交叉连接.
YOu had both implicit and explict joins on the products table (Make a note to never again use the implicit join syntax, it is a very bad programmin practice) and looking at the code, I suspect you were getting a cross join.
这篇关于SQL内部连接:数据库卡住的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!