hi

and if you add a key `nombre` for table tagente_modulo

select distinct(nombre)
from tagente_modulo
where nombre <> "delete_pending" and id_agente in
(
        select id_agente
        from tagente where id_grupo IN (
                select id_grupo
                from tusuario_perfil
                where id_usuario = "admin"
                and id_perfil IN (
                        select id_perfil
                        from tperfil where agent_view = 1
                )
        )
        OR
        (1 = (
                 SELECT is_admin FROM tusuario WHERE id_user = "admin"
             )
        )
        OR 0 IN (
                select id_grupo
                from tusuario_perfil
                where id_usuario = "admin"
                and id_perfil IN (
                        select id_perfil
                        from tperfil where agent_view = 1
                )
        )
);

# Query_time: 1.178337  Lock_time: 0.000223 Rows_sent: 1080  Rows_examined: 9065

this will change to:

# Query_time: 0.640100  Lock_time: 0.000103 Rows_sent: 1080  Rows_examined: 9065






2011/4/8 Sancho Lerena <sancho.lerena@artica.es>
El 08/04/11 11:13, Manuel Arostegui Ramirez escribió:

Thats a great discovery :)), go ahead with the commit !

> Even though it's not taking any important time, I really didn't like the
> fact of going thru almost half a milling rows everytime it runs, which is
> almost every second.
> There was never a condition to satisfy the "where" clause and therefore a
> cross join over 4 tables was being done over 500k rows, which is totally
> useless. To solve this I have included an index on tagente_modulo:
>
> KEY `modulo` (`id_modulo`)
>
> With this index the "problem" is solved:
>
> The query time got decreased yet it wasn't a big deal. Rows examined
> chilled :-)
> The CPU reduction in the machine is quite impressive (look attached graph)
> As well as the number of rows read everytime (look attached graph)
>
> If you guys agree, I can commit the schema change to the SVN.

--
Un saludo,

       Sancho Lerena
       Director Técnico
       http://www.artica.es
       c/ Preciados 44. 28013 Madrid
       Tel. +34-91-559-7222
       Mov. +34-627-934-649

Este mensaje se dirige exclusivamente a su destinatario y puede contener
información privilegiada o confidencial. Si no es vd. el destinatario
indicado, queda notificado de que la lectura, utilización, divulgación
y/o copia sin autorización está prohibida en virtud de la legislación
vigente. Si ha recibido este mensaje por error, le rogamos que nos lo
comunique inmediatamente por esta misma vía y proceda a su destrucción.

This message is intended exclusively for its addressee and may contain
information that is CONFIDENTIAL and protected by a professional
privilege or whose disclosure is prohibited by law.
If you are not the intended recipient you are hereby notified that any
read, dissemination, copy or disclosure of this communication is
strictly prohibited by law. If this message has been received in error,
please immediately notify us via e-mail and delete it.

------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
Pandora-develop mailing list
Pandora-develop@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/pandora-develop