Optimizando tablas

[xevisoyyo]

Hola estoy aprendiendo a optimizar y me he topado con una duda/problema

Los indices PRIMARY e INDEX
lo hago simple como ejemplo, tengo una tabla (InnoDB) llamada pruebas donde id (PRIMARY) y categoria (INDEX)

Por ejemplo en esta consulta

Código:

EXPLAIN SELECT id,categoria
FROM pruebas
WHERE categoria LIKE 'aventuras'

id ----------------- 1
select_type --------- SIMPLE
table - ------------- pruebas
type --------------- range
possible_keys ------ categoria_I
key ----------------- categoria_I
key_len ----------- 150
ref ------------------ NULL
rows --------------- 1
Extra - ------------- Using where; Using index

(bueno por ahi no creo que haya nada mal ¿no?)

Pero en esta otra consulta no se porque me repasa todas las filas

Código:

EXPLAIN SELECT id, categoria
FROM pruebas
WHERE id LIKE 5

id ----------------- 1
select_type --------- SIMPLE
table -------------- pruebas
type --------------- index
possible_keys ------ PRIMARY
key ---------------- categoria_I
key_len ----------- 150
ref ----------------- NULL
rows -------------- 14
Extra -------------- Using where; Using index

En esta ocasión type es index y si no tengo mal entendido revisa el campo id de todos los registros en este caso 14 mientras que antes solo necesitaba 1 si me lo podéis explicar

No se si esto se debe a que la base de datos elige el indice INDEX (categoria_I) en lugar del indice PRIMARY

Gracias y saludos

[pragone]

Perdona, pero por qué usas LIKE en vez de =.

Esto tiene una repercusión muy importante en la elección de Indices de MySQL. Con respecto a la optimización, es mejor que coloques un ejemplo no trivial que tengas... en caso contrario tampoco tiene mucha ciencia la optimización.

[gnzsoloyo]

Te paso un segmento de un apunte de cátedra respecto de la eficiencia de las consultas, que te puede orientar acerca del porqué varía el comportamiento según la forma que la misma se realice:

Br = cantidad de bloques que ocupa R
V(A,r) = cantidad de valores distintos del atributo A en la relación R.
Lr = Longitud de una tupla

SUPUESTO : R tiene una distribución UNIFORME.

SELECT * FROM R WHERE A θ “a”

Donde θ es { = , < , > , >= , <=, <>}

Costos de lectura: (CL)

a) Si Existe Indice agrupado (cluster) en A = ”a” entonces CL = Br / V(A,r)
b) Si existe Indice no agrupado (no cluster) en A = “a” entonces CL = Tr / V(A,r)
c) Si Existe Indice agrupado (cluster) en A ψ ”a” entonces CL = Br / 2 donde ψ es { < , > , <= , >= }
d) Si existe Indice no agrupado (no cluster) en A ψ “a” entonces CL = Tr / 2 donde ψ es { < , > , <= , >= }
e) Si no existe ningún índice entonces CL = Br

Ejemplo:
SELECT Np FROM proveedor WHERE ciudad = "M" ;

Datos:
Tr = 200 tuplas
Fb = 10 tuplas/ bloque
V(ciudad,proveedor) = 10

Si se cuenta con un índice cluster por ciudad, entonces el CL = Br / V(A,r)

Calculo de Br:
Br = Tr / fb = 200 tuplas / 10 tuplas/ bloque = 20 bloques.

El costo de lectura de la selección es de 2 accesos a bloque, o sea que es recomendable crear un índice cluster por ciudad para mejorar la performance.

A) ESTIMACIÓN DEL TAMAÑO DEL OUTPUT
PRODUCTO CARTESIANO
R X S = Tr . Ts

JUNTA NATURAL
R |X| S
Si R ∩ S = ∅ entonces Tr . Ts
Si R ∩ S = { clave de R } entonces <= Ts
Si R ∩ S = { clave de S } entonces <= Tr
Si R ∩ S = { atributo no clave } entonces Tr . Ts / V(A, r ) ó Tr .Ts / V(A,s)

Como verás, no es lo mismo consultar por "= "que por un LIKE, ni tampoco un índice cluster que uno no cluster (recordando que los índices cluster o densos contienen una alta selectividad y representan a los índices primarios, es decir aquellos que guardan la info física de los registros y sólo hay uno por tabla).

[pragone]

Eso estuvo denso pero interesante...
Añado que una parte muy importante de la optimización de Queries tiene que ver con la configuración de los caché de memoria para los índices. Si tienes memoria en la máquina, es mejor utilizarla porque le sacas poco provecho a los índices si tienes que leerlos de disco cada vez.

Mis dos centavos.

[xevisoyyo]

Hola de nuevo, lo del Like y el = es que no conocía muy bien la diferencia ya que aprendí hace poco y es algo que no me había parado a ver. Cuando uno aprende solo se le pasan las cosas mas tontas. Y con eso solucioné varias consultas.

gnzsoloyo me has dejado... jeje

La cosa es que de momento he usado la base de datos para cosas simples y aún recibiendo muchas visitas no he tenido ningún problema, pero poco a poco voy haciendo más cosas y me gustaría hacerlas bien de inicio

Digamos que hasta ahora sé hacer tablas con relación muchos a muchos y las consultas con JOIN, poco mas (al menos funcionan). Así que antes de hacer una base de datos más compleja o grande quisiera resolver algunas dudas.

Por ahora lo que me trae de cabeza o no entiendo es como con una tabla normal de 50 registros me ha de leer los 50 para darme uno solo (LIMIT 1). Eso me pasa cuando uso NOT IN o ORDER BY. He buscado sobre ORDER BY y en tablas con relación muchos a muchos, pensaba que hacia algo mal o no se, pero en una tabla única (sin relaciones con otras tablas) también me pasa así que no se si me pierdo algo. Por ejemplo con WHERE y LIMIT 1 solo me lee una fila pero a la que añado ORDER BY me las lee todas. He probado darle INDEX al campo por el que quiero que se ordenen pero tampoco. (El campo id tiene el indice PRIMARY)

Como no encuentro nada y la teoria me pierde un poco no sé si sabriais decirme porque pasa o que he de hacer porque aunque podría prescindir del NOT IN el ORDER BY me parece de lo mas necesario. Al menos para mis proyectos.

Muchas gracias pragone lo del LIKE se me pasó por alto y saludos

PD: no pongo ningún ejemplo porque lo del ORDER BY me pasa con cualquier consulta y supongo que tiene una explicación o consejo sin necesidad de ver más

[pragone]

Bueno. Recuerda que el Optimizador de queries del MySQL es muy inteligente pero no infalible.

En tu caso lo que puede estar pasando es que MySQL estime que por ser sólo 50 registros le es más rápido leerlos todos que usar un Indice que es posible que no tenga cargado en memoria.
Haz una prueba... genera muchos registros y prueba a hacer la query otra vez.

Esto lo he visto pasar alguna ocasión. Puedes forzarle el uso del índice con FORCE INDEX, pero es bastante extraño que haga falta.
Otra prueba que puedes hacer es forzar el índice y luego ejecutar la consulta nuevamente sin el FORCE, si en ese segundo SQL sí usa el índice, entonces es que MySQL había preferido no cargarlo.

[gnzsoloyo]

En esencia, al usar ORDER BY implica que el o los campos que usaremos para ordenar no figuran en los índices usados por la tabla. De esto, MySQL deduce que no es posible establecer a priori cuantas lecturas se deberán hacer ni tampoco desde qué punto deben hacerse. Por ello tiene que realizar una lectura completa de la tabla a fin de ordenarla previamente antes de buscar la secuencia de datos pedida.
Simplificando:
- MySQL (SQL Server también lo hace) debe leer toda la tabla cuando se usa ordenamiento en una columna no indexada porque no puede establecer a priori la posición de los mismos.
- Ademá, si la tabla es reducida, puede darse que leer la tabla completa use la misma cantidad de bloques de memoria que usaría utilizando el índice con el agregado que al reordenar ya tiene los datos y no tiene que hacer un segundo acceso a disco para buscar los 50 registros.
- El tema del uso o no de índices INDEX o PRIMARY se ve no tanto en el barrido de la tabla como en la duración en segundos de la consulta. Verificá cuanto dura en ambos casos, luego desactiva el primario y probá otra vez. Una de las formas es la mejor. Hay que encontrarla.
- Un tip adicional es establecer si realmente necesitás una consulta del tipo:

SELECT * FROM TABLA

es decir, una consulta que te traiga TODO. Usala solamente si vas a necesitar TODOS los datos, sino seleccioná los cámpos realmente útiles. El resto es basura en memoria y tiempo de procesador.