mysql 优化子查询_mysql – 使用子查询优化SQL

我想优化一个SQL语句,下面是原始的.

SELECT DISTINCT

p.productID,

p.filename,

p.model,

p.code,

p.manufacturerID,

f2.manufacturerName,

m.manufacturerName,

CONCAT('INSERT INTO distribItems(productID, manufacturer, code, distributorText) VALUES (',

CAST(p.productID AS CHAR),

', \'',

f2.manufacturerName,

'\', \'',

f2.code,

'\', \'',

f2.denumire,

'\') ;') INS

FROM

(SELECT

f.manufacturerName, f.categoryName, f.code, f.denumire, f.code_2

FROM

furnizorlist f

LEFT JOIN distribitems d ON

(d.manufacturer = f.manufacturerName

AND (d.code = f.code OR d.manufacturer LIKE 'DELL')

AND d.distributorText = LEFT(f.denumire, 450))

WHERE

productID IS NULL) f2,

products p,

manufacturers m

WHERE

f2.code_2 <> ''

AND (f2.code_2 = p.code_2 OR f2.code_2 = p.model_2)

AND p.manufacturerID = m.manufacturerID

AND m.manufacturerName = f2.manufacturerName

AND m.manufacturerName != 'Compatibil'

AND p.code != '1'

ORDER by p.filename ASC;

在我的电脑上大约需要34秒.

我的想法是将子查询编写为Join,并在Where子句中设置条件.

这是我令人难以置信的快速SQL：

SELECT DISTINCT

p.productID,

p.filename,

p.model,

p.code,

p.manufacturerID,

f.manufacturerName,

m.manufacturerName,

CONCAT('INSERT INTO distribItems(productID, manufacturer, code, distributorText) VALUES (',

CAST(p.productID AS CHAR),

', \'',

f.manufacturerName,

'\', \'',

f.code,

'\', \'',

f.denumire,

'\') ;') INS

FROM

furnizorlist f,

distribitems d,

#subquery end

products p,

manufacturers m

WHERE

d.manufacturer = f.manufacturerName

AND (d.code = f.code OR d.manufacturer LIKE 'DELL')

AND d.distributorText = LEFT(f.denumire, 450)

AND d.productID IS NULL

#subquery condions end (f and d tables)

# the next is a subquery result:

AND f.code_2 <> ''

AND (f.code_2 = p.code_2 OR f.code_2 = p.model_2)

AND p.manufacturerID = m.manufacturerID

AND m.manufacturerName = f.manufacturerName

AND m.manufacturerName != 'Compatibil'

AND p.code != '1'

ORDER by p.filename ASC;

如果我写解释Improved_sql,我会看到一个Impossible WHERE列.

我试过但却找不到为什么不可能.我检查了字段的兼容性：没有需要将int与varchar进行比较的情况.我找不到任何重大错误,这就是我在这里的原因.

WHERE子句中是否存在逻辑错误？比如要求一个字段为1,后来为2？

以ID结尾的字段是INT

以NAME结尾的字段,代码为varchar(255)

带文本的字段是450和8192 varchar(仅使用一个地方)

翻译：“denumire”的意思是“描述” – 或类似的东西:)

不确定哪个版本的mysql正在运行服务器端,可能是5.

如果我有充分的理由,我可以建议表结构更改,可能会调整代码.

编辑：

来自这里的不可能的地方：

EXPLAIN

SELECT

f.manufacturerName,

f.categoryName,

f.code,

f.denumire,

f.code_2

FROM

furnizorlist f

INNER JOIN

distribitems d ON (d.manufacturer = f.manufacturerName

AND (d.code = f.code

OR d.manufacturer LIKE 'DELL')

AND d.distributorText = LEFT(f.denumire, 450))

WHERE

productID IS NULL

备注：INNER JOIN,而不是LEFT JOIN.

EDIT2：

表：furnizorlist 42,751条记录

表：分配72,290条记录