Optimización de consultas MySql y PHP

[planmental]

Buenos Días a todos.

Yo aquí de nuevo con una de mis acostumbrados problemas de programación.

Mi problema es el siguiente:

Tengo una simple consulta:

Código MySQL:

SELECT * FROM inventario ORDER BY caja DESC  LIMIT 200
 

Editado: Código de programación no permitido en foros de Bases de Datos.
Leer las normas del foro, por favor.

La consulta anterior me da como resultado las primeras 200 consultas de la tabla, pero tiene 774 en total y quiero que las 774 se muestren de una sola consulta sin necesidad de pagina ni limitar el resultado.

Entonces, al consultar los 200 registros tarda aproximadamente 20 segundos. Ahora, para consultar los 774 tardaría muchos más.

Esta es mi tabla:

Código MySQL:

CREATE TABLE inventario (
  id_inventario int not null auto_increment primary key,
  actualizacion    date,
  caja      VARCHAR(20)   NOT NULL, 
  cliente  VARCHAR(50) NOT NULL, 
  localidad  VARCHAR(50) NOT NULL, 
  fecha_inicio  date, 
  fecha_final  date,
  nota VARCHAR(100) NOT NULL
);
 

No sé qué estoy haciendo mal, si es la programación en PHP o en MySql.

Espero me ayuden.

Un saludo

[Libras]

Agregale algunos indices a tu tabla para que tarde menos en mostrar los 700 registros :)

[planmental]

Iniciado por Libras

Agregale algunos indices a tu tabla para que tarde menos en mostrar los 700 registros :)

Hola, Buenos Días Libras.

Soy nuevo con eso de los índices (De hecho con todo MySql).

Y supongo que al crearlos debo de escribir "FORCE INDEX" en la consulta de MySql o así no funciona.

Saludos.

[gnzsoloyo]

Y supongo que al crearlos debo de escribir "FORCE INDEX" en la consulta de MySql o así no funciona.

NO. Lo que tienes que hacer es CREARLOS.

Por favor, comienza por leer el manual de referencia, en el punto que explica la creación de índices:

14.1.14 CREATE INDEX Syntax

FORCE INDEX se usa cuando ya los tienes y deseas que use alguno en especial, por fuera de su logia por default.

Por cierto, el manual tiene un capítulo entero dedicado a como usa MySQL los indices. Te recomiendo leerlo.

[planmental]

Iniciado por gnzsoloyo

NO. Lo que tienes que hacer es CREARLOS.

Por favor, comienza por leer el manual de referencia, en el punto que explica la creación de índices:

ALTER TABLE inventario ADD INDEX (caja)


FORCE INDEX se usa cuando ya los tienes y deseas que use alguno en especial, por fuera de su logia por default.

Por cierto, el manual tiene un capítulo entero dedicado a como usa MySQL los indices. Te recomiendo leerlo.

Sí yo sé en qué momento usarlo porque ya he creado un índice:

Código MySQL:

ALTER TABLE inventario ADD INDEX (caja)
 

Y a la vez he usado:

Código MySQL:

SELECT * FROM inventario FORCE INDEX (caja) ORDER BY caja DESC  LIMIT 200
 

Editado: Código de programación no permitido en foros de Bases de Datos.
Leer las normas del foro, por favor.

Estoy bien??

[gnzsoloyo]

¿Y estás seguro de que el índice se llama "caja"?

El indice genera un archivo en disco, y ese archivo es el que tiene el nombre del indice genera,do. Usualmente está relacionado con el nombre del campo, pero NO ES exactamente el mismo.

Código MySQL:

CREATE INDEX caja_idx ON inventario (caja);
 

Y luego lo usas:

Código MySQL:

SELECT * 
FROM inventario FORCE INDEX (caja_idx) 
ORDER BY caja DESC 
 LIMIT 200
 

[Libras]

como se te dijo dale una leida al manual de referencia.......

[planmental]

Iniciado por gnzsoloyo

¿Y estás seguro de que el índice se llama "caja"?

El indice genera un archivo en disco, y ese archivo es el que tiene el nombre del indice genera,do. Usualmente está relacionado con el nombre del campo, pero NO ES exactamente el mismo.

Código MySQL:

CREATE INDEX caja_idx ON inventario (caja);
 

Y luego lo usas:

Código MySQL:

SELECT * 
FROM inventario FORCE INDEX (caja_idx) 
ORDER BY caja DESC 
 LIMIT 200
 

Buenas.

Esta es la tabla que hay en phpmyadmin

Código HTML:

Acción  Nombre de la clave  Tipo    Único   Empaquetado Columna Cardinalidad    Cotejamiento    Nulo    Comentario
Editar Editar   Eliminar Eliminar   PRIMARY BTREE   Sí  No  id_inventario   763 A   No  
Editar Editar   Eliminar Eliminar   caja_idx    BTREE   No  No  caja    254 A   No
 

Todo está bien?

El PRIMARY ya estaba por default y el otro índice fue el que acabo de poner

[planmental]

Iniciado por Libras

como se te dijo dale una leida al manual de referencia.......

En eso estoy

[gnzsoloyo]

Iniciado por planmental

Buenas.

Esta es la tabla que hay en phpmyadmin

Nombre de la clave Tipo Único Empaquetado Columna Cardinalidad Cotejamiento Nulo Comentario
PRIMARY BTREE Sí No id_inventario 763 A No
caja_idx BTREE No No caja 254 A No

Todo está bien?

El PRIMARY ya estaba por default y el otro índice fue el que acabo de poner

Como se puede ver claramente en tu propio post, el índice se llama caja_idx , ese es el nombre con el que lo creó MySQL.

Haz el intento con ese mismo nombre.

[Libras]

Si te fijas tu indice se llama caja_idx no caja como pusiste en tu consulta

[planmental]

Una duda que tengo.

Se puede asignar cualquier campo de la tabla como índice, o tiene que tener algo en específico.

[gnzsoloyo]

Cualquier duda, ejecuta esto:

Código MySQL:

SELECT 
    table_name, constraint_name, TABLE_SCHEMA, constraint_type
FROM
    information_schema.table_constraints
WHERE
    UPPER(table_name) = 'INVENTARIO';
 

Iniciado por planmental

Una duda que tengo.

Se puede asignar cualquier campo de la tabla como índice, o tiene que tener algo en específico.

La creación de índices es una decisión de diseño del sistema, y de análisis de performance.
Dependerá de las condiciones a cumplir en las diferentes consultas, saber si conviene o no crearlos. No existen reglas definidas para eso.

No pierdas tiempo en consideraciones irrelevantes al asunto a solucionar. Estás perdiendo el foco en el problema.

Simplemente ten en cuenta lo que el manual indica de las restricciones a la creacion de lso indices, y luego dedícate a analizar qué es lo que necesitas en aquellos momentos en que las consultas resultan con baja performance.

[planmental]

Iniciado por gnzsoloyo

Cualquier duda, ejecuta esto:

Código MySQL:

SELECT 
    table_name, constraint_name, TABLE_SCHEMA, constraint_type
FROM
    information_schema.table_constraints
WHERE
    UPPER(table_name) = 'INVENTARIO';
 

La creación de índices es una decisión de diseño del sistema, y de análisis de performance.
Dependerá de las condiciones a cumplir en las diferentes consultas, saber si conviene o no crearlos. No existen reglas definidas para eso.

No pierdas tiempo en consideraciones irrelevantes al asunto a solucionar. Estás perdiendo el foco en el problema.

Simplemente ten en cuenta lo que el manual indica de las restricciones a la creacion de lso indices, y luego dedícate a analizar qué es lo que necesitas en aquellos momentos en que las consultas resultan con baja performance.

Este fue el resultado

table_name | constraint_name | TABLE_SCHEMA | constraint_type
inventario | PRIMARY | inventario_cajas | PRIMARY KEY

[gnzsoloyo]

Iniciado por planmental

Este fue el resultado

table_name | constraint_name | TABLE_SCHEMA | constraint_type
inventario | PRIMARY | inventario_cajas | PRIMARY KEY

Pues según eso, el supuesto índice no existe...

Postea esto:

Código MySQL:

SHOW CREATE TABLE inventario;
 

[planmental]

Iniciado por gnzsoloyo

Pues según eso, el supuesto índice no existe...

Postea esto:

Código MySQL:

SHOW CREATE TABLE inventario;
 

Este fue el resultado:

Table | Create Table
inventario | CREATE TABLE `inventario` (`id_inventario` int(...

[planmental]

Iniciado por gnzsoloyo

Pues según eso, el supuesto índice no existe...

Postea esto:

Código MySQL:

SHOW CREATE TABLE inventario;
 

Perdón me falto cambiarle las opciones:

Código MySQL:

inventario
CREATE TABLE `inventario` (
 `id_inventario` int(11) NOT NULL AUTO_INCREMENT,
 `actualizacion` date NOT NULL,
 `caja` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `cliente` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `localidad` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `fecha_inicio` date NOT NULL,
 `fecha_final` date NOT NULL,
 `nota` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id_inventario`),
 KEY `caja` (`caja`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci