为什么这个查询需要永远？ [英] Why does this query take forever?

查看：69 发布时间：2019/6/6 19:35:35 mysql

本文介绍了为什么这个查询需要永远？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

由于某些原因，我有一个相当大的（对我来说）查询，有许多内部

连接，访问远程服务器，它需要大约二十倍的时间

比同一数据库的大多数查询。

查询本身是在我的应用程序中以编程方式构建的，并且

示例如下。我希望小组中的某个人可能会对这个查询为何如此缓慢有所了解，这可能会为它提供一个更好的结构，这样我就可以回去了并重写我的代码

创建此类查询。

提前谢谢，Ike

" SELECT DISTINCT

chronology.id，status_id.status，chronology.complete d，chronology.completeddate

，chronology.completedtime，activities_id.activity，c hronology.activities_activ

ity，chronology.activities_attachment，chronology.ac tivities_available_to_all，

chronology.upcards_firstnamelastname，upcard_id.id，chronology.feedbackrequire

d，chronology.landondate，chronology.hasspecifictime， chronology.datetoperform，

chronology.timetoperform，chronology.duration，chron ology.weekends，chronology。

前缀，statusactivitieisid.id，associateresponsible .username，activities_usern

ameid.username，chronology.editFlag FRO M

年表，状态，活动，上传，状态激活，员工

INNER JOIN状态status_id on chronology.status_id = status_id.id

INNER加入活动活动_id

chronology.activities_id = activities_id.id

INNER JOIN上传upcard_id on chronology.upcard_id = upcard_id.id

INNER JOIN statusactivities statusactivitieisid on

chronology.statusactivitieisid = statusactivitieisid .id

INNER JOIN联营公司负责人

chronology.associateresponsible = associateresponsib le.id

INNER JOIN将activities_usernameid联系起来

chronology.activities_usernameid = activities_userna meid.id

WHERE chronology.upcard_id = 18"

解决方案

" Ike" < rx*@hotmail.com> schrieb im Newsbeitrag

新闻：xb ******************* @ newsread2.news.atl.eart hlink.net ...
出于某种原因，我有一个相当大的（对我来说）查询，有很多
内部联接，访问远程服务器，并且它比大多数查询要花费大约20倍
相同的数据库。
查询本身是在我的应用程序中以编程方式构建的，其示例如下所示。我希望小组中的某个人可能有
一些洞察为什么这个查询如此缓慢，建议可能有一个更好的结构，以便我可以重新进入并重写我的代码
创建此类查询。

提前致谢，IKE

SELECT DISTINCT

chronology.id，status_id.status，chronology.complete d ，chronology.completedda

te
，chronology.completedtime，activities_id.activity，c hronology.activities_act

iv
ity，chronology.activities_attachment，chronology .ac tivities_available_to_al

l，
chronology.upcards_firstnamelastname，upcard_id.id，chronology.feedbackrequi

re
d，chronology.landondate，chronology.hasspecifictime，chronology .datetoperfor

m，
chronology.timetoperform，chronology.duration，chronology.weekends，chronolog

y。
前缀，statusactivitieisid.id，associateresponsible .username，activities_use

rn ameid.username，chronology.editFlag FROM
年表，状态，活动，upcards，statusactivit ies，associates
INNER JOIN状态status_id on chronology.status_id = status_id.id
INNER JOIN活动activities_id
chronology.activities_id = activities_id.id
INNER JOIN上传upcard_id on chronology.upcard_id = upcard_id.id
INNER JOIN statusactivit statusactivitieisid on
chronology.statusactivitieisid = statusactivitieisid .id
INNER JOIN相关负责人
chronology.associateresponsible = associateresponsib le.id
INNER JOIN将activities_usernameid联系起来
chronology.activities_usernameid = activities_userna meid.id
WHERE chronology.upcard_id = 18"

首先我会检查是否所有这些都加入了表格在id

字段上有索引。如果他们不是，请创建它们并再次检查。

如果您没有重复项，则可以省略DISTINCT。保存数据库

很多工作。

罗伯特

嗯，所有的id都有索引。特别是，当我删除不同的....它

什么都不返回？ Ike

" Robert Klemme" <博****** @ gmx.net>在消息中写道

news：bp ************* @ ID-52924.news.uni-berlin.de ...

艾克 < rx*@hotmail.com> schrieb im Newsbeitrag
新闻：xb ******************* @ newsread2.news.atl.eart hlink.net ...
由于某种原因，我有一个相当大的（对我来说）查询，有大量的

内部
加入，访问远程服务器，它需要大约二十次

更长的
比对同一数据库的大多数查询。
查询本身是在我的应用程序中以编程方式构建的，
示例如下。我希望小组中的某个人可以

一些
洞察为什么这个查询如此缓慢，建议可能有更好的结构，这样我可以返回并重写我的代码来创建这样的查询。

提前致谢，Ike

SELECT DISTINCT

chronology.id，status_id.status，chronology.complete d，chronology.completedda
te

，chronology.completedtime，activities_id.activity，c hronology.activities_act
iv

ity，chronology.activities_attachment，chronology.ac tivities_available_to_al
l，

chronology.upcards_firstnamelastname，upcard_id.id，chronology.feedbackrequi
re

d，chronology.landondate，chronology.hasspecifictime，chronology.datetoperfor
m，

chronology.timetoperform，chronology.duration，chron ology.weekends，chronolog
y。

prefix，statusactivitieisid.id，associateresponsible .username，activities_use

ameid.username，chronology.editFlag FROM
年表，状态，活动，upcards，statusactivit ies，associates
INNER JOIN状态status_id on chronology.status_id = status_id.id
INNER JOIN活动activities_id
chronology.activities_id = activities_id.id
INNER JOIN上传upcard_id on chronology.upcard_id = upcard_id。 id
INNER JOIN statusactivities statusactivitieisid
chronology.statusactivitieisid = statusactivitieisid .id
INNER JOIN相关负责人
chronology.associateresponsible = associateresponsib le.id
INNER JOIN关联活动_usernameid
chronology.activities_usernameid = activities_userna meid.id
WHERE chronology.upcard_id = 18"

首先，我要检查所有这些连接表是否在id
字段中都有索引。如果他们不是，请创建它们并再次检查。

如果你没有重复，你可以省略DISTINCT。保存数据库很多工作。

robert

For some reason, I have a rather large (to me) query, with numerous inner
joins, accessing a remote server, and it is taking about twenty times longer
than most queries to the same database.
The query itself is built programmatically within my application, and
example of which is below. I am hoping someone in the group may have some
insight into why this query is so slow, suggesting perhaps a better
structure for it, such that I can go back in and rewrite my code that
creates such queries.

Thanks in advance, Ike

"SELECT DISTINCT
chronology.id,status_id.status,chronology.complete d,chronology.completeddate
,chronology.completedtime,activities_id.activity,c hronology.activities_activ
ity,chronology.activities_attachment,chronology.ac tivities_available_to_all,
chronology.upcards_firstnamelastname,upcard_id.id, chronology.feedbackrequire
d,chronology.landondate,chronology.hasspecifictime ,chronology.datetoperform,
chronology.timetoperform,chronology.duration,chron ology.weekends,chronology.
prefix,statusactivitieisid.id,associateresponsible .username,activities_usern
ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,associates
INNER JOIN status status_id on chronology.status_id=status_id.id
INNER JOIN activities activities_id on
chronology.activities_id=activities_id.id
INNER JOIN upcards upcard_id on chronology.upcard_id=upcard_id.id
INNER JOIN statusactivities statusactivitieisid on
chronology.statusactivitieisid=statusactivitieisid .id
INNER JOIN associates associateresponsible on
chronology.associateresponsible=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.id
WHERE chronology.upcard_id = 18"

解决方案

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:xb*******************@newsread2.news.atl.eart hlink.net...
For some reason, I have a rather large (to me) query, with numerous inner joins, accessing a remote server, and it is taking about twenty times longer than most queries to the same database.
The query itself is built programmatically within my application, and
example of which is below. I am hoping someone in the group may have some insight into why this query is so slow, suggesting perhaps a better
structure for it, such that I can go back in and rewrite my code that
creates such queries.

Thanks in advance, Ike

"SELECT DISTINCT
chronology.id,status_id.status,chronology.complete d,chronology.completedda
te ,chronology.completedtime,activities_id.activity,c hronology.activities_act
iv ity,chronology.activities_attachment,chronology.ac tivities_available_to_al
l, chronology.upcards_firstnamelastname,upcard_id.id, chronology.feedbackrequi
re d,chronology.landondate,chronology.hasspecifictime ,chronology.datetoperfor
m, chronology.timetoperform,chronology.duration,chron ology.weekends,chronolog
y. prefix,statusactivitieisid.id,associateresponsible .username,activities_use
rn ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,associates
INNER JOIN status status_id on chronology.status_id=status_id.id
INNER JOIN activities activities_id on
chronology.activities_id=activities_id.id
INNER JOIN upcards upcard_id on chronology.upcard_id=upcard_id.id
INNER JOIN statusactivities statusactivitieisid on
chronology.statusactivitieisid=statusactivitieisid .id
INNER JOIN associates associateresponsible on
chronology.associateresponsible=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.id
WHERE chronology.upcard_id = 18"

First I''d check whether all those joined tables have indexes on the id
field. If they don''t, create them and check again.

If you don''t have duplicates you can omit the "DISTINCT" saving the db a
lot of work.

robert

Hmmm, all the id''s do have indexes. Peculiarly, when I remove distinct....it
returns nothing? Ike

"Robert Klemme" <bo******@gmx.net> wrote in message
news:bp*************@ID-52924.news.uni-berlin.de...

"Ike" <rx*@hotmail.com> schrieb im Newsbeitrag
news:xb*******************@newsread2.news.atl.eart hlink.net...
For some reason, I have a rather large (to me) query, with numerous

inner
joins, accessing a remote server, and it is taking about twenty times

longer
than most queries to the same database.
The query itself is built programmatically within my application, and
example of which is below. I am hoping someone in the group may have

some
insight into why this query is so slow, suggesting perhaps a better
structure for it, such that I can go back in and rewrite my code that
creates such queries.

Thanks in advance, Ike

"SELECT DISTINCT

chronology.id,status_id.status,chronology.complete d,chronology.completedda
te

,chronology.completedtime,activities_id.activity,c hronology.activities_act
iv

ity,chronology.activities_attachment,chronology.ac tivities_available_to_al
l,

chronology.upcards_firstnamelastname,upcard_id.id, chronology.feedbackrequi
re

d,chronology.landondate,chronology.hasspecifictime ,chronology.datetoperfor
m,

chronology.timetoperform,chronology.duration,chron ology.weekends,chronolog
y.

prefix,statusactivitieisid.id,associateresponsible .username,activities_use
rn
ameid.username,chronology.editFlag FROM
chronology,status,activities,upcards,statusactivit ies,associates
INNER JOIN status status_id on chronology.status_id=status_id.id
INNER JOIN activities activities_id on
chronology.activities_id=activities_id.id
INNER JOIN upcards upcard_id on chronology.upcard_id=upcard_id.id
INNER JOIN statusactivities statusactivitieisid on
chronology.statusactivitieisid=statusactivitieisid .id
INNER JOIN associates associateresponsible on
chronology.associateresponsible=associateresponsib le.id
INNER JOIN associates activities_usernameid on
chronology.activities_usernameid=activities_userna meid.id
WHERE chronology.upcard_id = 18"

First I''d check whether all those joined tables have indexes on the id
field. If they don''t, create them and check again.

If you don''t have duplicates you can omit the "DISTINCT" saving the db a
lot of work.

robert

这篇关于为什么这个查询需要永远？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

为什么这个查询需要永远？ [英] Why does this query take forever?

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

为什么这个查询需要永远？ [英] Why does this query take forever?

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭