consulta con limit es muy lenta

[primary]

Tengo una tabla con unos 100000 registros y hago una consulta con limit pero es lenta cuando quiero mostrar los últimos registros.

Código MySQL:

SELECT * FROM  empresa LIMIT 0,200;
 

Código MySQL:

SELECT * FROM empresa  LIMIT 99000,99200;
 

La segunda consulta me tarda mucho que podría ser?
Saludos

[Libras]

tienes indices en tus tablas???

[primary]

Hola alguno tengo para otras consultas. Que tipo de I dice tengo que usar para esta consulta más o menos.
saludos y gracias.

[Libras]

los indices se usan por tablas no por consultas, quizas podrias usar un indice cluster en el campo de tu llave primaria(si es que tienes)

[primary]

Hola perdon, si en la tabla empresa ya uso indices ya que aveces las consultas tienen un where que aquí es donde actúan los índices. Las consultas son rápidas excepto el ejemplo que puse en la segunda consulta. Que te refieres con indices cluster?es posible teniendo más indices en la tabla?
Saludos

[Libras]

https://dev.mysql.com/doc/refman/5.0...dex-types.html

[primary]

Gracias.Me mirare el enlace con delicadeza.Saludos.

[primary]

Hola uso MyIsam creo que cluster funciona en innodb .

[primary]

Alguien me dice como crear un índice cluster en la llave primaria como dice Libras.

[primary]

Hola si con un índice cluster en la llave primaria te refieres a una pk ya la tenia en el campo id de la tabla pero mi consulta sigue siendo lenta.
Nadie me puede ayudar para acelerar este tipo consultas?

[julianerrimo]

Iniciado por primary

Tengo una tabla con unos 100000 registros y hago una consulta con limit pero es lenta cuando quiero mostrar los últimos registros.

Código MySQL:

SELECT * FROM  empresa LIMIT 0,200;
 

Código MySQL:

SELECT * FROM empresa  LIMIT 99000,99200;
 

La segunda consulta me tarda mucho que podría ser?
Saludos

LIMIT utiliza 2 parámetros: el primero indica dónde empieza y el segundo la cantidad de registros.

En tus ejemplo, la primera vez quieres mostrar 200 registros desde el primero (el 0), la segunda vez quieres mostrar 99200 registros desde el 99000. Obviamente tiene que tardar muchísimo más. La consulta "equivalente" sería:

Código MySQL:

SELECT * FROM  empresa LIMIT 99000,200;
 

Que traería 200 registros (igual que la primera) pero a partir del registro 99000.

[primary]

Iniciado por julianerrimo

LIMIT utiliza 2 parámetros: el primero indica dónde empieza y el segundo la cantidad de registros.

En tus ejemplo, la primera vez quieres mostrar 200 registros desde el primero (el 0), la segunda vez quieres mostrar 99200 registros desde el 99000. Obviamente tiene que tardar muchísimo más. La consulta "equivalente" sería:

Código MySQL:

SELECT * FROM  empresa LIMIT 99000,200;
 

Que traería 200 registros (igual que la primera) pero a partir del registro 99000.

Hola tienes razon fue un error de sintaxis pero en igual el LIMIT cuando son valores altos va lenta, como podria mejor esto, es posible alguien sabe alguna solucion?
saludos.

[julianerrimo]

Depende de muchas condiciones, índices, tipo de bbdd... pero puedes probar a hacerlo de otra manera a ver si te va más rápido:

Código MySQL:

SELECT * FROM  empresa WHERE id > 99000 ORDER BY id LIMIT 200;
 

[primary]

Hola julianerrimo esa consulta evidentemente es mucho mas rapida pero no me vale porque la tabla es dinamica y anteriormente eliminaba registros que caducaban pero ahora le añadi un campo de eliminado:si es decir que la consulta anterior completa seria con un where eliminado="no".
En resumen que no seria efectivo ya que los id no siempre estan, esto lo quiero para un paginador.

Código MySQL:

SELECT * FROM  empresa WHERE id > 99000 AND eliminado="no" ORDER BY id LIMIT 200;
 

Gracias por tu colaboracion, la verdad que busque por internet pero no encuentro nada, cualquier ayuda se agradece porque estoy saturado con el tema, no se si quizas alguna configuracion del servidor mysql pueda acelerar mi consulta, saludos.

[Libras]

para ti que es que la consulta este lenta?? ya probaste regresando solo algunos campos de la tabla, no todos??? para que necesitas todos los campos?? tienes algun otro filtro que puedas aplicar? cual es el total de registros???

[gnzsoloyo]

Meto la cuchara en el plato, para hacer una observación que hay que tener en cuenta...

Siendo que LIMIT apica sobre resultaod de la consulta, y a priori MySQL sólo sabe cual es el ultimo valor de ID de la tabla, pero no cuantos registors efectivamente existen en ella aún, el único camino que tiene apra realizar lecturas con saltos tan grandes es hacer un full table scan hasta encontrar los 99000 registros, para luego recuperar los 200 siguientes.
Ahora bien, además de esto, hay que tener en cuenta que un DBMS no "lee" los registros como una sola entidad continua de registros, sino que va almacenando grupos de registros en bloques de memoria que tienen un tamaño máximo. Mientras más registros deba leer, mas bloques deberá leer, escribir y descartar, con el aumento de accesos a datos y cambios de contexto del sistema. La consecuencia inmediata de eso es que un LIMIT como el descripto es por definición un espanto de performance.
Debe recordarse además que un bloque de datos se carga con registros íntegros, por lo que eventualmente puede haber desperdicio de memoria si la suma de bytes de lso registors deja espacio sin usar por ser menor a la longitud de un registro... Lo que empeora la situación en consultas de miles o millones de regstros.
Esto que digo, es, además, afectado por el buffer de consultas y las capacidades de red, que sin duda pueden verse ahogadas por consultas de mucho peso.
Esa es una de las razones por la que nunca se recomienda hacer consultas que generen reportes de semejante extensión, y también por qué existen herramientas específicas para construir reportes y listados.

En defnitiva, a menos que realices un filtrado sobre otros datos y columnas, las cuales conviene estén indexadas, esa consulta nunca funcionará en forma eficiente.
Podrás mejorar algunas configuraciones, e incluso usar otro hardware, pero siemrpe tendrás un piso de performance que no podrás bajar porque la misma consulta es ineficiente.

En la empresa que trabajo tenemos un caso semejante para ciertos listados que puede devolver cientos de miles de registros, y no se permiten realizar consultas en las aplicaciones, que abarquen la totalidad de resultados. Para esos casos, cuando iden algo de tanto impacto, se deriva al área de Bussines Intelligence, para que constuya los reporrtes con otras herramientas, ya que el SQL puro es insuficiente para tales necesidades.

[primary]

Hola primero comentar que no tengo ningún filtro que añadir solo el de eliminado y no tiene indice.Respecto a la lentitud la consulta con limit 0,200 me tarda 0.09s con limit 99000,200 me tarda 2.8s para mi es lento.No se que opinais?
Respecto a gnzsoloyo ante todo gracias por tu ayuda y comentarte.La tabla no tendrá más de 100.000 registros en este caso no tengo alternativa o lo dejo así o no creo paginador o no lo hago efectivo.No entiendo muy bien que hacer en un caso así?
Vi por Internet un truco como hacer un select dentro de otro selectivo con join donde el select segundo obtiene los id del rango y luego con join muestra los registros igual al id la verdad que mejora la consulta pero al aplicarle el filtro eliminados=no en el segundo select de la consulta esta se hace aún más lenta.
No se sigo muy indeciso de que puedo hacer.
Saludos.

[Libras]

2.8 seg para 100,000 registros se me hace un buen tiempo(tomando en cuenta que haces un select *), no se donde ves la lentitud.......

[gnzsoloyo]

Vi por Internet un truco como hacer un select dentro de otro selectivo con join donde el select segundo obtiene los id del rango y luego con join muestra los registros igual al id la verdad que mejora la consulta pero al aplicarle el filtro eliminados=no en el segundo select de la consulta esta se hace aún más lenta.

No creas todo lo que leas en Internet. Muchas veces son soluciones que solo aplican a casos puntuales, o escenartios limitados, y el uso de subconsultas es un arma de doble filo.
Para tu caso, eso es basura pura.

Para el caso, la mejor solución ya te la dieron:

Código MySQL:

SELECT * FROM  empresa WHERE id > 99000 ORDER BY id LIMIT 200;
 

pero tu problema es el campo "eliminado", que tioenes definido como VARCHAR, obviamente en función de tu query:

Código MySQL:

SELECT * FROM  empresa WHERE id > 99000 AND eliminado="no" ORDER BY id LIMIT 200;
 

Por eficiencia, es mejor no hacer validaciones de "SI"/"No", sino usar valores de BIT, BOOLEAN o INT, es decir, validarpor 0 ó 1, que es mucho más rápido.

Modifica la tabla y cambia el tipo de columna a TINYINT o BIT, y asignale DEFAULT '0', y realiza la comparación, por ejemplo:

Código MySQL:

SELECT * FROM  empresa 
WHERE id > 99000 
AND NOT eliminado 
ORDER BY id LIMIT 200;
 

o bien:

Código MySQL:

SELECT * FROM  empresa 
WHERE id > 99000 
AND eliminado = 0
ORDER BY id LIMIT 200;
 

No estoy seguro de cuanta mejora lograrás, pero dado tu contexto, es lo más que veo.

[primary]

Hola gracias por las respuestas vamos por parte, Libras la consulta no es lenta pero si la comparas con las primeras páginaciones si lo es y el usuario puede pensar que va mal y eso no quiero.
gnzsoloyo la solución me la dieron si pero si página en una en una todo va bien pero si quiere ver la 5 página o la última ya tendría errores de datos no exactos con páginas debido a que los campos están eliminados, a no ser que primero verifique cuantos id están eliminados para luego añadirse los a la consulta que me dijo Libras.
En fin está forma de hacer dos consultas es aconsejable o no?

[Libras]

Ahora otra cosa, para que necesitas el select *?? es necesario???

[primary]

Iniciado por Libras

Ahora otra cosa, para que necesitas el select *?? es necesario???

No es necesario solo es como ejemplo en mis cálculos tengo los campos que necesito.
Saludos

[gnzsoloyo]

Iniciado por primary

Hola gracias por las respuestas vamos por parte, Libras la consulta no es lenta pero si la comparas con las primeras páginaciones si lo es y el usuario puede pensar que va mal y eso no quiero.
gnzsoloyo la solución me la dieron si pero si página en una en una todo va bien pero si quiere ver la 5 página o la última ya tendría errores de datos no exactos con páginas debido a que los campos están eliminados, a no ser que primero verifique cuantos id están eliminados para luego añadirse los a la consulta que me dijo Libras.
En fin está forma de hacer dos consultas es aconsejable o no?

¿Entendiste que te estoy sugiriendo que reemplaces el campo que usas como VARCHAR por otro que sea TINYINT, que usarás para el mismo uso, sólo que con valores 0/1?

La idea es que cumpla exactamente lo mismo que ahora, pero haciendo un tipo de validación más eficiente a nivel de consultas, ya que las comparaciones numéricas son más rápidas que las de cadenas de texto...

No debería haber cambios en el resultado, sino en la velocidad de la query.

¿Se enteinde?

[primary]

Iniciado por gnzsoloyo

¿Entendiste que te estoy sugiriendo que reemplaces el campo que usas como VARCHAR por otro que sea TINYINT, que usarás para el mismo uso, sólo que con valores 0/1?

La idea es que cumpla exactamente lo mismo que ahora, pero haciendo un tipo de validación más eficiente a nivel de consultas, ya que las comparaciones numéricas son más rápidas que las de cadenas de texto...

No debería haber cambios en el resultado, sino en la velocidad de la query.

¿Se enteinde?

si lo entendí perfectamente y lo cambie pero así el paginador no es exacto debido que tengo campos eliminados.De esa forma la consulta es rapidísima pero fallan los id.

[gnzsoloyo]

¿De qué campos eliminados estás hablando?
Me parece que no nos estás decribiendo el escenario correctamente, proque si la query funciona, y el problema es de DATOS, es algo completamente distinto, y con soluciones diferentes.

Explcia claramente este último tema.

[primary]

Me intento explicar, esta consulta es para un paginador entonces usando la consulta que tengo la que postee arriba la primera página seria con el limit 0,300.
La segunda página seria limit 200,200 así sucesivamente para cada página.
Pero que pasa con la consulta que me proponéis si quiero mostrar la primera página no pasa nada pero para la segunda si algún registro esta marcado como eliminado no me muestra los registros que pertenece es decir para la primera página no sería problema para la segunda seria así id>200 limit 200 pero que pasa si antes de los 200 registros tengo id como eliminados, si fuera así por ejemplo uno eliminado esta segunda página me mostraría un registro repetido con anterioridad en ja primera página, no se si se entiendo el problema.
En este caso tendría que crear una consulta antes para comprobar si ahí id eliminados en el ejemplo que puse para paginar la segunda página teniendo en cuenta que ahi un id eliminado la segunda consulta que seria el select seria así id>201 limit 200.
Entonces mi oregunta es, Es aconsejable crear dos consultas?

[gnzsoloyo]

Perdona que te lo diga así, pero tu duda carece de sentido. parece que no estás entendiendo como funciona el LIMIT, ni tampoco como opera el WHERE.

El LIMI aplica sobre el resultado final, es decir, sólo sobre los registros que cumplen con el WHERE, sin otras consideraciones. Esto último implica que el LIMIT sólo puede devovler los registros que NO están marcados como eliminados, y sólo cuenta ESOS.
Es imporsible para el LIMIT contar aquellos registros marcados como eliminados, porque la consulta NO LOS DEVUELVE. Es como si NO EXISTIERAN.

En los hechos, sólo podría devolverte en la segunda página un registro que muestra en la primera, si y sólo si en el interín entre ambas consutlas, alguien quitó la marca de eliminado de un registro que debía aparecer en el primer bloque. El corrimiento generado hacia adelante podría mover un registro de la primera a la segunda pagina, pero solo en ese contexto.
AL igual que en ese caso, si en el interín, otro usuario marca como eliminado un registro que aparecía en la primera página, se generará en la segunda ejecución un corrimietno igual hacia atrás, no mostrando un registro que antes era válido.
Este tipo de situaciones no son sencillas de resolver, pero sólo se producen en este contexto, y suelen ocurrir en sistemas concurrentes. Para evitarlos hay ciertas técnicas, como por ejemplo, tomar el mayor id recuperado del primer bloque y enviar a buscar los siguientes X registros posteriores a ese.
En estos casos, si, efectivamentye, la consulta necesita ser creada deforma dinámica, para agregar o quitar condiciones, según se necesite.
Pero la base de la query, no cambia.

¿Se entiende?

[primary]

Iniciado por gnzsoloyo

Perdona que te lo diga así, pero tu duda carece de sentido. parece que no estás entendiendo como funciona el LIMIT, ni tampoco como opera el WHERE.

El LIMI aplica sobre el resultado final, es decir, sólo sobre los registros que cumplen con el WHERE, sin otras consideraciones. Esto último implica que el LIMIT sólo puede devovler los registros que NO están marcados como eliminados, y sólo cuenta ESOS.
Es imporsible para el LIMIT contar aquellos registros marcados como eliminados, porque la consulta NO LOS DEVUELVE. Es como si NO EXISTIERAN.

En los hechos, sólo podría devolverte en la segunda página un registro que muestra en la primera, si y sólo si en el interín entre ambas consutlas, alguien quitó la marca de eliminado de un registro que debía aparecer en el primer bloque. El corrimiento generado hacia adelante podría mover un registro de la primera a la segunda pagina, pero solo en ese contexto.
AL igual que en ese caso, si en el interín, otro usuario marca como eliminado un registro que aparecía en la primera página, se generará en la segunda ejecución un corrimietno igual hacia atrás, no mostrando un registro que antes era válido.
Este tipo de situaciones no son sencillas de resolver, pero sólo se producen en este contexto, y suelen ocurrir en sistemas concurrentes. Para evitarlos hay ciertas técnicas, como por ejemplo, tomar el mayor id recuperado del primer bloque y enviar a buscar los siguientes X registros posteriores a ese.
En estos casos, si, efectivamentye, la consulta necesita ser creada deforma dinámica, para agregar o quitar condiciones, según se necesite.
Pero la base de la query, no cambia.

¿Se entiende?

Hola gnzsoloyo tres cosas, primero agradecerte que sigas ayudandome, segundo no tienes que pedirme perdon ya que estoy aqui para aprender de los maestros y tres como novato que soy sigo teniendo dudas y la vuelvo a plantear.

Se como funciona el LIMIT es decir el paginador como lo tengo hasta ahora creado si son paginas de 200 registros cada una la primera pagina seria asi:

primera pagina

Código MySQL:

SELECT * FROM empresa WHERE eliminado=0 LIMIT 0,200
 

segunda pagina

Código MySQL:

SELECT * FROM empresa WHERE eliminado=0 LIMIT 200,200
 

decima pagina

Código MySQL:

SELECT * FROM empresa WHERE eliminado=0 LIMIT 2000,200
 

De esta manera(que es como la tengo)funciona perfectamente el paginador ya que como dices afecta el LIMIT a los registros afectado tengan filtro o no, Pero mi problema era que si quiero ver una de las ultimas paginas el paginador se vuelve lento.

Entonce la solucion que me disteis fue esta consulta para pagina:

primera pagina

Código MySQL:

SELECT * FROM empresa WHERE eliminado=0 AND id>0 LIMIT 200
 

o tambien asi:

Código MySQL:

SELECT * FROM empresa  WHERE eliminado=0 LIMIT 200
 

pero veo este problema, es decir en circunstancias normales la primera pagina me devolvera las filas con los id del 1 al 200 como ultimo id, quedando de esta forma la segunda pagina de paginacion:

segundapagina

Código MySQL:

SELECT * FROM empresa WHERE id>200 LIMIT 200
 

seguiria funcionando bien el paginador en este caso la segunda pagina, pero que pasa si en la primera pagina tengo un registro eliminado por poner uno digamos el primero con id 1, el rango de id que me devolveria seria del 2 al 201 porque me devuelve siempre 200, entonces la segunda pagina que seria asi:

segundapagina

Código MySQL:

SELECT * FROM empresa WHERE id>200 LIMIT 200
 

me devolveria el id 201 repetido, en definitiva se que el LIMIT devuelve solo filas afectadas pero al usar el WHERE id> para paginar esta el pequello conflicto de que el rango de id no es siempre el cuadrante, por eso insisto que si es buena idea crear dos consultas, una para saber cuantos id estan eliminados para luego añadirles al paginador, en este ejemplo teniendo un id eliminado en la primera pagina tendria que sumarle 1 a los 200 id del WHERE quedando asi:


segundapagina

Código MySQL:

SELECT * FROM empresa WHERE id>201 LIMIT 200
 

de esta forma me aseguro no repetir registros, bueno a la espera de las respuestas que estoy aqui para aprender, saludos.

[gnzsoloyo]

El ID que debes poner en esos filtrados no es el valor del LIMIT, sino el mayor ID devuelto en la consulta anterior.
Bien podría suceder que terminase siendo algo como:

Código MySQL:

...
WHERE  eliminado =0 AND ID > 32176
LIMIT 200;
 

[primary]

Iniciado por gnzsoloyo

El ID que debes poner en esos filtrados no es el valor del LIMIT, sino el mayor ID devuelto en la consulta anterior.
Bien podría suceder que terminase siendo algo como:

Código MySQL:

...
WHERE  eliminado =0 AND ID > 32176
LIMIT 200;
 

Si funcionaria si paginara en uno en uno pero que pasa si quiere paginar de la página 1 a la 200? Es aquí donde esta el problema como se que ultimo id pertenece a esa página para mostrarla.
Saludos.