我该如何优化此查询？ [英] How can i optimize this query?

问题描述

我正在使用名为pandorafms的网络监控工具。在控制台视图中，它有一个名为tactical view的页面。此页面显示所有监视代理程序和监视模块的摘要。我的问题与网络无关。它是关于编码，在该页面上运行一个SQL查询。每次执行大约需要20秒。我需要减少执行时间。我的问题是如何优化它，是否有任何配置需要mysql数据库？如果需要;我该如何配置？我是编程新手。我需要一些帮助。



-sql查询如下：

I''m working with network monitoring tool called pandorafms. In console view, it has a page named tactical view. this page shows summery of the all monitoring agents and monitoring modules. my question is not related to networking. it is about coding, there is a sql query running on that page. it take about 20sec to execute every time. i need to reduce the executing time. my question is how do i optimize it and is there any configuration need for mysql database? if need; how do i configure it? I''m new to the programming.I need a little help.

-sql query is given below-

# Query_time: 17.830726  Lock_time: 0.000093 Rows_sent: 1  Rows_examined: 0
SET timestamp=1356521617;
SELECT COUNT(*) FROM ( SELECT DISTINCT tagente.id_agente
                                                FROM tagente, tagente_modulo, tagente_estado
                                                WHERE tagente.id_agente = tagente_modulo.id_agente
                                                AND tagente_modulo.id_agente_modulo = tagente_estado.id_agente_modulo

                                                AND tagente.id_grupo IN (9)
                                                AND tagente.id_agente NOT IN (SELECT tagente.id_agente
                                                FROM tagente_estado, tagente, tagente_modulo
                                                WHERE tagente_estado.id_agente = tagente.id_agente
                                                AND tagente_estado.id_agente_modulo = tagente_modulo.id_agente_modulo
                                                AND tagente.disabled = 0
                                                AND tagente_modulo.disabled = 0
                                                AND estado = 1
                                                AND tagente_estado.utimestamp != 0
                                                AND tagente.id_grupo IN (9)
                                                group by tagente.id_agente)
                                                AND tagente.id_agente NOT IN (SELECT tagente.id_agente
                                                FROM tagente_estado, tagente, tagente_modulo
                                                WHERE tagente_estado.id_agente = tagente.id_agente
                                                AND tagente_estado.id_agente_modulo = tagente_modulo.id_agente_modulo
                                                AND tagente.disabled = 0
                                                AND tagente_modulo.disabled = 0
                                                AND estado = 2
                                                AND tagente_estado.utimestamp != 0
                                                AND tagente.id_grupo IN (9)
                                                group by tagente.id_agente)
                                                AND tagente.id_agente IN (SELECT tagente.id_agente
                                                FROM tagente_estado, tagente, tagente_modulo
                                                WHERE tagente_estado.id_agente = tagente.id_agente
                                                AND tagente_estado.id_agente_modulo = tagente_modulo.id_agente_modulo
                                                AND tagente.disabled = 0
                                                AND tagente_modulo.disabled = 0
                                                AND estado = 3
                                                AND tagente_estado.utimestamp != 0
                                                AND tagente.id_grupo IN (9)
                                                group by tagente.id_agente) ) AS t;

推荐答案

可能是这个link [ ^ ]可帮助您开始优化。他们逐步解释了优化查询的过程，并使用他的技术解决了我的问题。

may be this link[^] help you to start your optimization. They explained step by step procedure to optimize query and i have used his technique to solve my problems.

作为查询的查看者（而不是数据库结构），我有一些步骤。我想推荐：



1.你有使用IN / NOT IN的子查询。我建议你将它们转换为EXIST / NOT EXIST。您可以查看以下链接： http：// dev .mysql.com / doc / refman / 5.0 / en / exists-and-not-exists-subqueries.html [ ^ ]



2。另一个最有趣的事情是你需要覆盖索引。请阅读覆盖索引。请浏览其他人提供的链接，我也恳请您分享有关此主题的最佳链接。



问候

Pawan

As a viewer of your query (and not your database structure)there are certain steps I''d like to recommend:

1. There are sub queries that you have using IN/NOT IN. I''d recommend that you convert them to EXIST/NOT EXIST. You may have a look at this link: http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html[^]

2. The other most interesting thing is that you will need to have covering indexes. Please read on covering indexes. Please go through the links provided by others and I''d also urge you to share the best possible links on this topic.

Regards
Pawan

这篇关于我该如何优化此查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！