在mysql中长时间退出连接 [英] Left join long time in mysql

问题描述

亲爱的所有人，我有下面的联接查询，需要4分钟以上才能完成.
我正在寻找一种优化查询的方法，请问有什么想法吗?下面是详细信息:
第一个查询:
从 viewm16journeysq v

  select  *  s(0.5718s)

第二查询:

 选择 * 来自 test2 其中 类型 不是 在中( 1 ， 2 ， 3 ， 4 )
以0.1940s(0.0041s)的时间提取了9108行



而波纹管是左连接

 选择 cast(`v`.`SDU参考编号` as 签名) AS `SDU参考号`，
t2.DABAccount`，t2.发送到银行
来自`viewm16journeysq` v
左 加入 test2 t2  on  v. SDU参考号`= t2.SDURN  and 
t2.类型<>  1   and  t2.类型<> ;  2   and  t2.类型<> ;  3   and  t2.类型<> ;  4  
 



9106行以747.9791s(4.1869s)的价格获取





最后一次左联接大约需要4分钟以上，我真的不明白问题所在，因为第1和第2个查询执行得很快.
解决方案
 
 MySQL运行缓慢这样的联接. postgres仍然可以解决它，但是当事情变得更加复杂时，您将不得不切换到Microsoft SQL Server或Oracle.

您可以尝试以下操作:

-在test2.type 
上设置索引
-在test2.SDURN 
上设置索引
-在viewm16journeysq上设置索引.`SDU参考编号`

-删除演员表

-尝试使用WHERE子句:

 ... 左 加入 test2 t2 位置 t2.类型 不是 在中( 1 ， 2 ， 4 )
 

-test2.type可以具有哪些值?您可以将子句更改为test2.type> 4吗?

-viewm16journeysq是表还是视图?在后一种情况下，更改查询以使其使用基础表而不是视图.
 
 
Dear All, I have bellow left join query and it takes more than 4 minutes to complete.

I am looking for a way to optimize the query, any ideas please? bellow is the details:

1st Query:

select * from viewm16journeysq v
 9106 rows fetched in 0.3945s (0.5718s)

2nd Query:

select * from test2 where type not in (1,2,3,4)
 9108 rows fetched in 0.1940s (0.0041s)



and bellow is the left join

select cast(`v`.`SDU Reference No` as signed) AS `SDU Reference No`,
t2.`DABAccount`,t2.`Sent to Bank`
from `viewm16journeysq` `v`
left join test2 t2 on v.`SDU Reference No` = t2.SDURN and
t2.type  <> 1 and t2.type <> 2 and t2.type <> 3 and t2.type <> 4




9106 rows fetched in 747.9791s (4.1869s)





the last left join takes approx more than 4 mins, I really dont understand where is the problem as 1st and 2nd query executes fast.
解决方案
MySQL is slow when it comes to such JOINs. postgres would still cope with it, but when things become more complicated you''ll have to switch to Microsoft SQL Server or Oracle.

Some things you can try:

- set an index on test2.type

- set an index on test2.SDURN

- set an index on viewm16journeysq.`SDU Reference No`

- remove the cast

- try a WHERE clause:

...left join test2 t2 on v.`SDU Reference No` = t2.SDURN 
WHERE t2.type not in (1,2,3,4)


- which values can test2.type have? Can you change the clause to test2.type>4?

- is viewm16journeysq a table or a view? In the latter case, change your query such that it uses the underlying tables instead of the view.


                        
这篇关于在mysql中长时间退出连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！