OT: Optimizando selects anidados

[Sebastian Bassi]

Tengo una query que en localhost tarda 144s, pero en el hosting
compartido donde la quiero implementar me resetea el MySQL server
(debe ser un mecanismo de proteccion para que un gil como yo no tire
abajo las aplicaciones del resto).

Ahora estoy "partiendo" la query en partes y haciendo algunas
operaciones con Python, pero por ahi tiro aca la query problematica
por si alguno puede darme una mano con cosas para optimizar asi medias
ovbias (por ejemplo statements repetidos quizas se puedan evitar,
aunque tenian entendido que para eso está la cache).
Aca está mi "SELECT asesino":

SELECT * FROM hits WHERE miRNA IN (
SELECT miRNA FROM targets WHERE Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
)))
AND Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metab

[Ernesto Savoretti]

Post by Sebastian Bassi
...
SELECT * FROM hits WHERE miRNA IN (
SELECT miRNA FROM targets WHERE Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
)))
AND Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
));

Es difícil opinar sin conocer exactamente tu estructura de datos, pero prima
facie daría la impresión de que tener que repetir un mismo query ("AND Name
IN...", que aparece en un inner select y en el más externo) claramente debe
ser un punto a optimizar. Estás seguro que necesariamente tenés que hacerlo
así?.
Segundo, pero no menos importante, como siempre que hablemos de optimización
de performance en consultas a motores BD, está el tema de los índices, que
cuando están bien diseñados te pueden aumentar la performance por órdenes de
magnitud, y cuando están mal...mejor hacelo a mano.
En síntesis, espero que esto te sugiera alguna idea de qué podría estar
funcionando mal.

[Mariano Mara]

Post by Sebastian Bassi
Tengo una query que en localhost tarda 144s, pero en el hosting
compartido donde la quiero implementar me resetea el MySQL server
(debe ser un mecanismo de proteccion para que un gil como yo no tire
abajo las aplicaciones del resto).
Ahora estoy "partiendo" la query en partes y haciendo algunas
operaciones con Python, pero por ahi tiro aca la query problematica
por si alguno puede darme una mano con cosas para optimizar asi medias
ovbias (por ejemplo statements repetidos quizas se puedan evitar,
aunque tenian entendido que para eso está la cache).
SELECT * FROM hits WHERE miRNA IN (
SELECT miRNA FROM targets WHERE Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
)))
AND Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
));

¿Índices? ¿Cantidad de registros por tablas? ¿Cardinalidad de
metabolite.metabolite?

Me parece que lo estás complicando de más: lo primero siempre es probar
con joins y luego pasar a otras cosas si no queda otra.

Probá con esto [1] [2]

select e.* from hits e inner join
(select c.miRNA from targets c inner join
(select a.name from bin a inner join metabolite b on (a.bin=b.bin)
where b.metabolite like '%mannose%') d
on (c.name=d.name)) f on (e.mirna=f.mirna)

esto podría ser más claro quizás -a lo sumo será necesario dar vuelta el
orden de las uniones para facilitar la búsqueda-:

select a.* from hits a, targets b, bin c, metabolite d
where a.mirna=b.mirna and b.name=c.name and c.bin=d.bin and
d.metabolite like '%mannose%'

La última parte de la instrucción realmente no la entendí ¿para qué
volvés a filtrar el nombre si ya lo hiciste antes? Seguro que hay algo
que no estoy viendo.

[1] Reconozco no tener experiencia en mysql -y eso me juega en contra
para saber como el optimizador empuja las instrucción en los predicados
correspondientes- pero espero que sea lo suficiente ansi como para que
el motor lo entienda.
[2] Es demasiado domingo a la noche como para que arme las tablas en
alguna de mis bases así que es posible que haya algún parentesis donde
no debería y/o otros errores de sintaxis que no deberían ser dificiles
de resolver.

[Santiago Suarez Ordoñez]

Post by Mariano Mara

Post by Sebastian Bassi
Tengo una query que en localhost tarda 144s, pero en el hosting
compartido donde la quiero implementar me resetea el MySQL server
(debe ser un mecanismo de proteccion para que un gil como yo no tire
abajo las aplicaciones del resto).
Ahora estoy "partiendo" la query en partes y haciendo algunas
operaciones con Python, pero por ahi tiro aca la query problematica
por si alguno puede darme una mano con cosas para optimizar asi medias
ovbias (por ejemplo statements repetidos quizas se puedan evitar,
aunque tenian entendido que para eso está la cache).
SELECT * FROM hits WHERE miRNA IN (
SELECT miRNA FROM targets WHERE Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
)))
AND Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
));

¿Índices? ¿Cantidad de registros por tablas? ¿Cardinalidad de
metabolite.metabolite?
Me parece que lo estás complicando de más: lo primero siempre es probar con
joins y luego pasar a otras cosas si no queda otra.
Probá con esto [1] [2]
select e.* from hits e inner join
(select c.miRNA from targets c inner join
(select a.name from bin a inner join metabolite b on (a.bin=b.bin)
where b.metabolite like '%mannose%') d
on (c.name=d.name)) f on (e.mirna=f.mirna)
esto podría ser más claro quizás -a lo sumo será necesario dar vuelta el
select a.* from hits a, targets b, bin c, metabolite d
where a.mirna=b.mirna and b.name=c.name and c.bin=d.bin and
d.metabolite like '%mannose%'
La última parte de la instrucción realmente no la entendí ¿para qué volvés a
filtrar el nombre si ya lo hiciste antes? Seguro que hay algo que no estoy
viendo.
[1] Reconozco no tener experiencia en mysql -y eso me juega en contra para
saber como el optimizador empuja las instrucción en los predicados
correspondientes- pero espero que sea lo suficiente ansi como para que el
motor lo entienda.
[2] Es demasiado domingo a la noche como para que arme las tablas en alguna
de mis bases así que es posible que haya algún parentesis donde no debería
y/o otros errores de sintaxis que no deberían ser dificiles de resolver.
---------------------------------------------------------------------
PyAr - Python Argentina - Sitio web: http://www.python.com.ar/

Y si usas EXISTS?
No se si es mas optimo que usar JOIN pero seguro que es más optimo que usar IN.

SELECT *
FROM tablasantiago aliassanti
WHERE aliassanti.campo = 'Y'
AND EXISTS (SELECT * FROM otratabla alias2 WHERE aliassanti.campo2 =
alias2.campo2)

Espero que se entienda, no soy un gran capo del SQL pero esta es una
de las reglas basicas para algunas consultas que hacemos en el laburo.


-

[Matías A. Bellone]

Post by Sebastian Bassi
Tengo una query que en localhost tarda 144s, pero en el hosting
compartido donde la quiero implementar me resetea el MySQL server
(debe ser un mecanismo de proteccion para que un gil como yo no tire
abajo las aplicaciones del resto).
Ahora estoy "partiendo" la query en partes y haciendo algunas
operaciones con Python, pero por ahi tiro aca la query problematica
por si alguno puede darme una mano con cosas para optimizar asi medias
ovbias (por ejemplo statements repetidos quizas se puedan evitar,
aunque tenian entendido que para eso está la cache).
SELECT * FROM hits WHERE miRNA IN (
SELECT miRNA FROM targets WHERE Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
)))
AND Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
));

evitate todas las consultas anidadas con:

SELECT
hits.*
FROM
hits,
targets,
BIN,
metabolite
WHERE
metabolite.metabolite LIKE '%mannose%'
AND BIN.BIN = metabolite.BIN
AND targets.Name = BIN.Name
AND hits.miRNA = targets.miRNA

que es básicamente lo mismo.

Es probable, sin embargo que el problema no sea la forma en la que hacés
la consulta sino la consulta en sí. Si la tabla "metabolite" tiene
muchos registros no te conviene hacer un LIKE sobre ellos; menos que
menos con %a% que es lo mismo que decir "que contenga ese texto". Es
lentísimo.

Coincido con lo que dijeron otros, sin embargo, habría que ver la
estructura de la base de datos y la cantidad de registros en cada tabla
para poder optimizar.

Saludos,
Toote

[Ezequiel Gutesman]

Creo que no, lo que logras con una enumeracion asi en el FROM es un producto
cartesiano entre tablas, luego foltrado segun las condiciones del WHERE. Me
parece que ocupa mucho espacio en memoria.

Lo mejor es replantear la query con los joins correspondientes (para lo cual
terecomeindo hacerte un diagrama de entidad-relacion) y hacer indices de
manera "inteligente", asi podes sumarle variantes de Index hints (
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html) donde le
"recomendas" al engine de Mysql que indices usar o cuales no.

Si queres te ayudo a armar el diagrama y a pensar los indices y la query
(solo necesitamos las tablas que usas y la relacion entre ellas), no por la
lista, obvio, porque la lista es de python :D.

salu2
gutes

Post by Sebastian Bassi

Post by Sebastian Bassi
Tengo una query que en localhost tarda 144s, pero en el hosting
compartido donde la quiero implementar me resetea el MySQL server
(debe ser un mecanismo de proteccion para que un gil como yo no tire
abajo las aplicaciones del resto).
Ahora estoy "partiendo" la query en partes y haciendo algunas
operaciones con Python, pero por ahi tiro aca la query problematica
por si alguno puede darme una mano con cosas para optimizar asi medias
ovbias (por ejemplo statements repetidos quizas se puedan evitar,
aunque tenian entendido que para eso está la cache).
SELECT * FROM hits WHERE miRNA IN (
SELECT miRNA FROM targets WHERE Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
)))
AND Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
));

SELECT
hits.*
FROM
hits,
targets,
BIN,
metabolite
WHERE
metabolite.metabolite LIKE '%mannose%'
AND BIN.BIN = metabolite.BIN
AND targets.Name = BIN.Name
AND hits.miRNA = targets.miRNA
que es básicamente lo mismo.
Es probable, sin embargo que el problema no sea la forma en la que hacés la
consulta sino la consulta en sí. Si la tabla "metabolite" tiene muchos
registros no te conviene hacer un LIKE sobre ellos; menos que menos con %a%
que es lo mismo que decir "que contenga ese texto". Es lentísimo.
Coincido con lo que dijeron otros, sin embargo, habría que ver la
estructura de la base de datos y la cantidad de registros en cada tabla para
poder optimizar.
Saludos,
Toote
---------------------------------------------------------------------
PyAr - Python Argentina - Sitio web: http://www.python.com.ar/

[Matías Bellone]

Post by Ezequiel Gutesman
Creo que no, lo que logras con una enumeracion asi en el FROM es un producto
cartesiano entre tablas, luego foltrado segun las condiciones del WHERE. Me
parece que ocupa mucho espacio en memoria.

En realidad no. MySQL hace el filtrado antes de hacer el producto
cartesiano (o al menos eso es lo que pasa en los casos en los que lo
uso). Sería equivalente a los JOINs siempre que las cosas en el WHERE
estén en el orden correspondiente con la ventaja añadida que al
momento de hacer los JOINs ya tienes gran parte de los datos
filtrados.

Puedo estar diciendo macanas pero no he visto un uso de memoria alto
al momento de hacer queries de este tipo en tablas con cientos de
millones de registros e inclusive resultan más rápidas que hacer los
JOINs correspondientes.

Post by Ezequiel Gutesman
Lo mejor es replantear la query con los joins correspondientes (para lo cual
terecomeindo hacerte un diagrama de entidad-relacion) y hacer indices de
manera "inteligente", asi podes sumarle variantes de Index hints
(http://dev.mysql.com/doc/refman/5.0/en/index-hints.html) donde le
"recomendas" al engine de Mysql que indices usar o cuales no.

Pero para eso necesitas usar los índices para filtrar. En este caso,
se está haciendo una búsqueda de texto extensiva (LIKE '%TEXTO%') por
lo que no hay índice que valga.

Post by Ezequiel Gutesman
Si queres te ayudo a armar el diagrama y a pensar los indices y la query
(solo necesitamos las tablas que usas y la relacion entre ellas), no por la
lista, obvio, porque la lista es de python :D.

Saludos,
Toote

[Ezequiel Gutesman]

Post by Ezequiel Gutesman

Post by Ezequiel Gutesman
Creo que no, lo que logras con una enumeracion asi en el FROM es un

producto

Post by Ezequiel Gutesman
cartesiano entre tablas, luego foltrado segun las condiciones del WHERE.

Me

Post by Ezequiel Gutesman
parece que ocupa mucho espacio en memoria.

En realidad no. MySQL hace el filtrado antes de hacer el producto
cartesiano (o al menos eso es lo que pasa en los casos en los que lo
uso). Sería equivalente a los JOINs siempre que las cosas en el WHERE
estén en el orden correspondiente con la ventaja añadida que al
momento de hacer los JOINs ya tienes gran parte de los datos
filtrados.

Esto es cierto, pero me parece ( a mi, que no soy DBA) mas claro no
depender del orden en que pongo las condiciones del where, quedando mas
claro (de nuevo, para mi) con inner joins. Aparte, es en este caso
particular, donde el engine no hace relamente un cross product cuando
deberia (porque se lo pido yo) sino que optimiza usando el mismo algoritmo
de join, pero si tengo que recomendar, recomiendo el join, ya que no depende
de la implementacion si hace o no el cross product.

Post by Ezequiel Gutesman
Puedo estar diciendo macanas pero no he visto un uso de memoria alto
al momento de hacer queries de este tipo en tablas con cientos de
millones de registros e inclusive resultan más rápidas que hacer los
JOINs correspondientes.

Post by Ezequiel Gutesman
Lo mejor es replantear la query con los joins correspondientes (para lo

cual

Post by Ezequiel Gutesman
terecomeindo hacerte un diagrama de entidad-relacion) y hacer indices de
manera "inteligente", asi podes sumarle variantes de Index hints
(http://dev.mysql.com/doc/refman/5.0/en/index-hints.html) donde le
"recomendas" al engine de Mysql que indices usar o cuales no.

Pero para eso necesitas usar los índices para filtrar. En este caso,
se está haciendo una búsqueda de texto extensiva (LIKE '%TEXTO%') por
lo que no hay índice que valga.

Ese es el unico indice que no te sirve, ni B+ ni nada. Pero para el resto si
te sirven (para los joins por ejemplo, usando algoritmos de join mas
performantes, tipo INLJ), mas cuando tenes que joinear muchos registros
entre muchos++ .

Post by Ezequiel Gutesman

Post by Ezequiel Gutesman
Si queres te ayudo a armar el diagrama y a pensar los indices y la query
(solo necesitamos las tablas que usas y la relacion entre ellas), no por

la

Post by Ezequiel Gutesman
lista, obvio, porque la lista es de python :D.

Saludos,
Toote

salu2
gutes

Post by Ezequiel Gutesman
--
Web: http://www.enespanol.com.ar
---------------------------------------------------------------------
PyAr - Python Argentina - Sitio web: http://www.python.com.ar/

[Ricardo Kirkner]

Post by Matías Bellone
Pero para eso necesitas usar los índices para filtrar. En este caso,
se está haciendo una búsqueda de texto extensiva (LIKE '%TEXTO%') por
lo que no hay índice que valga.

Bueno, teniendo en cuenta que la búsqueda de texto es un tema principal
para tus queries, una cosa que te recomiendo mires es si existe algún
Full Text Search Index para MySQL. Yo usé uno que había para Postgres, y
que generaba indices sobre distintas partes del texto y así podías hacer
búsquedas con LIKE de manera bastante rápida.

Es medio long-shot, pero puede hacer la diferencia, ya que como dijeron,
los índices son un tema crucial para la performance de la base.

saludos

ricardo

[Sebastian Bassi]

2008/6/3 Ricardo Kirkner <***@gmail.com>:
....

tus queries, una cosa que te recomiendo mires es si existe algún Full Text
Search Index para MySQL. Yo usé uno que había para Postgres, y que generaba

Si, existe. Lo tendré en cuenta para la proxima, ahora ya pasé a la
siguiente fase del programa.

[Lucas G. Devescovi]

Pero Sebastiš¢n, me lešª toda la cadena y al final no dijiste lo mš¢s
importante!
O sea, al fin y al cabo, a cuanto lograste bajar tu consulta?

Saludos!

Post by Ricardo Kirkner
....

tus queries, una cosa que te recomiendo mires es si existe algš²n Full

Text

Search Index para MySQL. Yo usšŠ uno que habšªa para Postgres, y que

generaba
Si, existe. Lo tendršŠ en cuenta para la proxima, ahora ya pasšŠ a la
siguiente fase del programa.
--
Sebastiš¢n Bassi (¥»¥Ð¥¹¥Æ¥£¥¢¥ó). Diplomado en Ciencia y Tecnologšªa.
Curso Biologia molecular para programadores: http://tinyurl.com/2vv8w6
Mostrš¢ tu cš®digo: http://www.pastecode.com.ar
GPG Fingerprint: 9470 0980 620D ABFC BE63 A4A4 A3DE C97D 8422 D43D

[Sebastian Bassi]

Pero Sebastián, me leí toda la cadena y al final no dijiste lo más
importante!
O sea, al fin y al cabo, a cuanto lograste bajar tu consulta?

Buena pregunta. No medí el tiempo ahroa porque la hice con Python
(fijate un par de mensajes atras que mostré el código) y ahora se
completa. Antes el tiempo si lo veia porque lo hacia directamente
sobre MySQL (y cuando termina te da el tiempo). Decime alguna
instruccion que pueda poner antes y despues del codigo para medir
cuanto tarda en ejecutarse y lo pruebo. Lo que es seguro, que tarda
muchisimo menos porque se ejecuta rapido pero no puedo saber exacto
porque es en un server y no se cual es el overhead que provoca el
server, la latencia de mi conexion y cuando del SELECT en sí mismo,
por eso te pregunto de como poner un timer antes y despues, asi
imprimo eso y nos sacamos la duda.

Saludos,
SB.

[Manuel Kaufmann]

Post by Sebastian Bassi
Decime alguna
instruccion que pueda poner antes y despues del codigo para medir
cuanto tarda en ejecutarse y lo pruebo.

Función decoradora:

def tiempo(funcion):
def nueva(*args):
print 'Funcion', funcion.func_name
inicial = time.time()
valor = funcion(*args)
final = time.time()
print 'Demoro', final-inicial, 'segs'
return valor
return nueva

Saludos!

[Lucas G. Devescovi]

Gracias humitos por la respuesta!!

Seba, te explico, no tengo ni la maspu de Python, o sea, 2 boludeces nada más.
Estoy en la lista por la curiosidad on-board que tengo desde que nací, además
soy feliz estando en una lista de buena gente y siempre aprendo algo!!

Abrazos para todos!

----- Original Message -----
From: "Manuel Kaufmann" <humitos-***@public.gmane.org>
To: <pyar-***@public.gmane.org>
Sent: Thursday, June 05, 2008 4:34 PM
Subject: Re: [pyar] OT: Optimizando selects anidados

[Sebastian Bassi]

Antes que nada gracias a todos los que contestaron. No pongo la
estructura de la tabla aca porque por un lado es un poco OT y por otro
porque no queria una optimización completa, solamente queria ver si
tenia algun error muy tonto en el SQL mio, algo que salte a la vista
rapidamente al ojo de alguien mas experimentado. El tema del like %
XXX% no se puede evitar porque la naturaleza de los datos.
Aca está como lo solucioné, como habia busquedas que se hacian 2
veces, la hice una y luego generé una lista con los resultados para
hacer una query metiendo la lista directamente:

sel1="SELECT Name from BIN WHERE BIN in (SELECT BIN from metabolite WHERE \
Metabolite like '%"+stextQTL+"%')"
cursor.execute(sel1)
names=set()
for x in cursor:
names.add('"'+x[0]+'"')
sel2="SELECT miRNA from targets WHERE Name IN ("+",".join([k for k in
names])+")"
cur2.execute(sel2)
MrnaS=set()
for x in cur2:
MrnaS.add('"'+x[0]+'"')
sel3="SELECT * FROM hits WHERE miRNA in ("+",".join([k for k in
MrnaS])+") and Name in ("+",".join([k for k in names])+")"
cur3.execute(sel3)

Post by Sebastian Bassi

Post by Sebastian Bassi
Tengo una query que en localhost tarda 144s, pero en el hosting
compartido donde la quiero implementar me resetea el MySQL server
(debe ser un mecanismo de proteccion para que un gil como yo no tire
abajo las aplicaciones del resto).
Ahora estoy "partiendo" la query en partes y haciendo algunas
operaciones con Python, pero por ahi tiro aca la query problematica
por si alguno puede darme una mano con cosas para optimizar asi medias
ovbias (por ejemplo statements repetidos quizas se puedan evitar,
aunque tenian entendido que para eso está la cache).
SELECT * FROM hits WHERE miRNA IN (
SELECT miRNA FROM targets WHERE Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
)))
AND Name IN (
SELECT Name FROM BIN WHERE BIN IN (
SELECT BIN FROM metabolite WHERE Metabolite LIKE '%mannose%'
));

SELECT
hits.*
FROM
hits,
targets,
BIN,
metabolite
WHERE
metabolite.metabolite LIKE '%mannose%'
AND BIN.BIN = metabolite.BIN
AND targets.Name = BIN.Name
AND hits.miRNA = targets.miRNA
que es básicamente lo mismo.
Es probable, sin embargo que el problema no sea la forma en la que hacés la
consulta sino la consulta en sí. Si la tabla "metabolite" tiene muchos
registros no te conviene hacer un LIKE sobre ellos; menos que menos con %a%
que es lo mismo que decir "que contenga ese texto". Es lentísimo.
Coincido con lo que dijeron otros, sin embargo, habría que ver la estructura
de la base de datos y la cantidad de registros en cada tabla para poder
optimizar.
Saludos,
Toote
---------------------------------------------------------------------
PyAr - Python Argentina - Sitio web: http://www.python.com.ar/