Click here to Skip to main content
11,490,007 members (67,438 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL MySQL
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;
Posted 30-Dec-12 18:24pm
Edited 1-Jan-13 18:04pm
v3
Comments
jibesh at 31-Dec-12 2:16am
   
it may be possible to fine tune your query but without knowing too much about your table design and the data you want to display its hard for other person to judge your query.

You may google to find different optimization technique.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Comments
Rajesh Varma Buddaraju at 2-Jan-13 0:21am
   
instead of COUNT(*) specify a column
instead of subqueries use temporary tables or CTE
use nolock in case of report data
Rai Pawan at 2-Jan-13 2:47am
   
Thanks Rajesh for augmenting the list :)
- Pawan

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 595
1 Andy Lanng 233
2 OriginalGriff 213
3 Maciej Los 170
4 _duDE_ 141
0 Sergey Alexandrovich Kryukov 9,608
1 OriginalGriff 8,475
2 Sascha Lefèvre 3,419
3 Maciej Los 3,106
4 Richard Deeming 2,370


Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 1 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100