[SOLUCIONADO] Optimización de una tabla, ¿influye el orden de las columnas? ¿las indexo?

[ricardobit]

Pensaba que esto sería fácil, pero no sé como acelerar las consultas. Estoy buscando saber qué columnas debo indexar y que tipo (INDEX, FULLTEXT...), y si el orden de las columnas afecta al rendimiento de las consultas MySQL.

Tengo una sola tabla con más de un millón de apellidos en Español, y estoy preparando un programa estadístico para que unos investigadores humanos puedan investigar las raíces de los apellidos, etimología y cosas así.

En la tabla hay que hacer cosas sencillas, por ejemplo:

• Mostrar cuantos apellidos comienzan con "r", o con "rod", o cualquier letra o cadena.
• O cuales acaban en "guez" o en "ez" o en "z", o cualquier letra o cadena.
• O cuales apellidos tienen 5 letras, o el numero de letras elegido.
• O cuales contienen una cadena concreta de letras como "ine" o "iguez".
• O una mezcla de toda las opciones anteriores a la vez!

Al principio tenía una tabla así:

Código:

# Columna   Tipo        Cotejamiento   Atributos  Nulo
1 id        int(11)     UNSIGNED       No          AUTO_INCREMENT
2 apellido  char(25)    utf8_bin       No

La indexación era sobre la columna "id".

Código:

Nombre de la clave Tipo   Único  Empaquetado  Columna  Cardinalidad  Cotejamiento    Nulo
PRIMARY            BTREE  Sí     No           id       1000939       A

Problema: Si por ejemplo se le pone que busque apellidos que al inicio tienen "a", la primera consulta tardaba entre 30 y 90 segundos! algo impresentable. Las siguientes consultas supongo que MySQL cachéa las consultas y tardaba 0.07 segundos.



Modifiqué la tabla con dos mejoras:

• En lugar de cotejar con "utf8_bin", coteja con "utf8_spanish_ci", así si se busca apellidos que tengan "a" al inicio, en los resultados aparecen los que comienzan con "a", "á", "Á", "Ä", etc. Aunque la consulta se ha vuelto más lenta que antes, pero abarca más resultados.
• Pensé que en lugar de buscar en la columna apellido, "pre-procesar" la tabla y que hubiese una columna con la letra inicial de cada apellido, otra columna con la letra final, otra con el número de letras, y otra con las letras "raras" (las que no están en el alfabeto inglés, como acentos, eñes, etc). De esta manera si se busca un apellido que empieza con "rod", busca en la columna "inicio" las que tienen el valor "r" (la primera letra de "rod") y así MySQL no tiene que recorrer toda la tabla. O si se busca apellidos con la cadena "añon", como detecto que hay una "letra rara" (la ñ) pues buscaría en la columna "letraras" las que tengan una "ñ" y también me evito recorrer toda la tabla.

Código:

# Columna   Tipo        Cotejamiento   Atributos  Nulo
1 id        int(11)     UNSIGNED         No     AUTO_INCREMENT
2 apellido  char(25)    utf8_spanish_ci  No            
3 numletras tinyint(3)  UNSIGNED         No     Ninguna
4 inicio    char(1)     utf8_spanish_ci  No     Ninguna
5 final     char(1)     utf8_spanish_ci  No     Ninguna
6 letraras  char(6)     utf8_bin         No     Ninguna

La indexación también la hago sobre la columna "id" y no sé si hay que indexar otra/s.

Código:

Nombre de la clave Tipo   Único  Empaquetado  Columna  Cardinalidad  Cotejamiento    Nulo
PRIMARY            BTREE  Sí     No           id       1000939       A

Preguntas:

1. ¿Alguien sabe que columnas tengo que indexar para que esto funcione bien? ¿y que tipo de indexación? ¿o las pongo a todas como INDEX y va que chuta?
   
2. En algunas webs he leído que cuando se repiten gran cantidad de datos en una misma columna, es una perdida de tiempo indexar la información, en otras afirman lo contrario, dicen que siempre es mejor indexarlas pero no todas las columnas o no sirve de nada. ¿Quién tiene razón?.
   
3. ¿Cuando hago la consulta en SQL, tengo que respetar el orden en que he puesto las columnas? Es que he leído que MySQL indexa mejor, pero me extraña que aun hoy en día, el motor de MySQL no elija el orden más apropiado.
   
4. ¿Debería cambiar el orden de las columnas, o da igual?

Por ejemplo, para buscar apellidos que empiezan con "rodr", acaban en "z" y tenga 9 letras, yo haría:

Código MySQL:

SELECT apellido FROM TablaApellidos 
WHERE (inicio='a' AND final='z' AND numletras=9 AND apellido LIKE 'rodr%' AND apellido LIKE '%z');
 

¿O al estar en la tabla primero la columna apellido, luego numletras, luego inicio, luego final, etc se tiene que definir exactamente en ese orden?

Código MySQL:

SELECT apellido FROM TablaApellidos 
WHERE (apellido LIKE 'rodr%' AND apellido LIKE '%z'  AND numletras=9 AND inicio='a' AND final='z');
 

¿O todo este tinglado que he realizado para optimizar la tabla es una solemne tontería y estoy matando moscas a cañonazos?

Para acabar, pongo unos ejemplos inventados del contenido de la tabla, por si no me hubiese explicado bien en algo:

Código:

id   Apellido   numletras inicio final  letraras
1    Ocañá      5         o      a      ñá
2    Aba        3         a      a         
3    Ababe      5         a      e         
4    Ácronita   8         a      a      á
5    Ñarónico   8         ñ      o      ñó

Muchas gracias por vuestra atención.

[quimfv]

Para las consultas que estas planteando el índice sobre el id no sirve absolutamente de nada. Es la PK y por definición tendrá un índice asociado pero para las consultas que planteas no sirven de nada ni el índice ni la PK auto incremental en si misma. Ni siquiera sirve para que no tengas apellidos repetidos, nada impediría ingresar Pérez con id=1 y seguidamente Pérez con id=2.

Antes de hacer todo ese preproceso, que te servirá en algunos casos y en otros no yo de ti leería un poco de teoría sobre índices y para empezar crearía un índice UNIQUE sobre apellido, con este ya te aseguraras que no haya repeticiones.

Con el índice sobre apellido la mayoría de casos en que supones que se va a recorrer toda la tabla ya no se hará.

También afirmas que con los campos calculados que estas proponiendo evitaras recorridos completos sobre la tabla, pues si esos campos no están a su vez indexados el recorrido se producirá igualmente.


Teoria general

http://es.wikipedia.org/wiki/%C3%8Dn...se_de_datos%29


Más concretamente en el caso de MySQL

http://dev.mysql.com/doc/refman/5.0/...l-indexes.html

Decidiendo por el motor, como forzar el uso de un indice

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Funciones que te serviran para leer partes de un apellido

http://dev.mysql.com/doc/refman/5.0/...functions.html

[ricardobit]

Muchas gracias Quim, creo que con sólo decirme que el índice sobre la id es inútil, me has aclarado ya un montón de cosas que había leído en los últimos días y no había forma de comprender. Mi error ha sido pensar que la columna id era obligatoria.

(he tenido que buscar que era PK, para quién no lo sepa como yo, significa Primary Keyword) :P


Voy a hacer lo que comentas, crear un índice UNIQUE sobre apellido, para asegurarme que no hayan repeticiones.

En tu segundo enlace, http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html (que por cierto es genial) comentan lo siguiente:

-------------------------
For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL cannot use an index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).
-------------------------

Ya sólo me queda una duda:

¿Siguiendo el ejemplo de dev.mysql.com, debería crear índices tipo (col1), (col2), (col3), (col1, col2), (col1, col3), (col2, col3) y (col1, col2, col3)?

En mi caso:
(Apellido)
(numletras)
(inicio)
(final)
(letraras)
(Apellido, numletras)
(Apellido, inicio)
(Apellido, final)
(Apellido, letraras)
(Apellido, numletras, inicio)
(Apellido, numletras, final)
(Apellido, numletras, letraras)
... etc... hasta llegar a:
(Apellido, numletras, inicio, final, letraras)

¿O es una barbaridad crear índices para cada columna y sus variantes?

¿O según tu experiencia, es mejor olvidarme de todo y hacer sólo una columna (Apellido) indexada como UNIQUE y trabajar sólo con ella?

La tabla de apellidos no se actualizará una vez creada (sólo es de lectura) y es posible que tenga un volumen enorme de consultas tipo SELECT-FROM-WHERE, con picos que podrían llegar a 10 consultas por segundo.

No sabes cuanto te lo agradezco. Con lo bien que se me da programar, con las BBDD me siento muy torpe y me cuesta entender sus conceptos.

Muchas gracias por tu atención.

[quimfv]

Esos campos "preprocesados" que propones son campos calculados, es decir simplemente repiten una información que ya esta en la base de datos, su principal peligro es que en alguna modificación /actualización de la base de datos no se calculen de nuevo dejando la tabla incoherente.

Es decir para agregrar Gómez si no estaba en la tabla no basta con agregarlo hay que calcular todos los campos.

Si la lista de apellidos es definitiva, no se van ha agregar nuevos no habría problema puesto que una vez calculados los campos no va ha haber modificaciones. Con lo que se podrían crear, pero como te he dicho ya en el primer post si no estan indexados no mejoraran mucho las busquedas.

Yo crearia el indice sobre apellido y veria que performance me dan las consultas...

Ojo el campo id no es obligatorio, pero una PK si lo és. En este caso apellido puede ser la PK con lo que ya tendrías el índice único creado. Las PK numéricas auto incrementales se usan para reducir el tamaño de los índices y de las FK (foreing keys) en otras tablas del sistema. Como no has hablado de mas tablas apellido podría ser tranquilamente la PK de tu tabla.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html (que por cierto es genial)

Manual de referencia

[ricardobit]

He avanzado más hoy que en los últimos siete días.

En muchos sitios de Internet hablan como optimizar tablas complejas, pero no encontraba como hacerlo con tablas enormes, pero muy simples.

Muchas gracias de nuevo. :)

Iniciado por quimfv

Yo crearía el indice sobre apellido y vería que performance me dan las consultas...

Ojo el campo id no es obligatorio, pero una PK si lo és. En este caso apellido puede ser la PK con lo que ya tendrías el índice único creado.

[ricardobit]

Ups, me sale error "#1062 - Duplicate entry" cuando intento hacer que la columna Apellido sea un índice de tipo UNIQUE. Esto se debe a que hay apellidos que se escriben con acento y sin acento.

La solución sería pasar el cotejamiento de la columna Apellido de "utf8_spanish_ci" a "utf8_bin", pero entonces si se busca por ejemplo apellidos que empiezan con "A", ya no aparecen los que comienzan con "á", "à", "ä", etc.

¿Qué puedo hacer? ¿Dejo la columna "id" como una key primaria y defino la columna "Apellido" como INDEX? ¿o no serviría de nada?

[gnzsoloyo]

Ups, me sale error "#1062 - Duplicate entry" cuando intento hacer que la columna Apellido sea un índice de tipo UNIQUE. Esto se debe a que hay apellidos que se escriben con acento y sin acento.

Una pregunta: ¿Estás completamente seguro de que no se repite ningún apellido entre dos registros cualesquiera? ¿Es una tabla de apellidos únicos?
¿CUál es exactamente la utilidad de esa tabla? ¿En qué tipo de proyecto la usas?

[ricardobit]

Hola Gnzsoloyo, gracias por tu interés.

Todos los apellidos son únicos en la tabla, no se repiten, salvo que algunos tienen acento y otros no. Y posiblemente hayan vocales con diéresis, circunflejos, ç, ñ, etc.

Per ejemplo, existen registros así:
Abreu
Abréu

Pero no hay ningún registro duplicado tipo:
Abreu
Abreu

La diferencia del acento en los apellidos se debe a que algunos siguen la ortografía del idioma español, pero otros provienen de otros idiomas como el francés o el catalán, donde las reglas de acentuación son diferentes.

He probado de cambiar el cotejamiento de la columna 'palabra' de 'utf8_spanish_ci' a 'utf8_bin' y entonces admite sin problemas indexar la columna como UNIQUE.

Pero en utf8_bin, al buscar por '%eu' me sale sólo "Abreu" y no aparece "Abréu".

Si intento hacer lo mismo en 'utf8_spanish_ci', entonces aparece el error:

Código:

ALTER TABLE `Sql11XXX_1`.`Apellidos` ADD UNIQUE `apellido` ( `apellido` ) 
MySQL ha dicho:
#1062 - Duplicate entry 'Abréu' for key 2

Supongo que se debe a que en 'utf8_spanish_ci', con o sin acento da igual, para MySQL es un registro duplicado.

Esta tabla es para investigación genealógica, raíces de los apellidos, etimología, estadísticas y cosas así. Consta de una sola tabla, no se relaciona con ninguna otra tabla. Parece todo muy simple, pero la dificultad es que hay un millón de registros y que a veces puede tener picos altos de personas haciendo diferentes consultas al mismo tiempo como cuando se utilice en cursos sobre el tema.

[gnzsoloyo]

En ese contexto es inevitable usar utf8_bin, con lo que vas a tener que lidiar con las distinciones de acentos. No podrás evitarlo, y eventualmente tendrás que usar funciones almacenadas creadas por ti para realizar el "ajuste" de las vocales acentuadas.
En esencia es hacer que reemplace dinámicamente el dato obtenido para que no tome en cuenta los acentos. Es un poco engorroso, pero puede funcionar.
Por otro lado, podrías usar funciones de expresiones regulares, por ejemplo.
Hay varios caminos posibles.

[quimfv]

Per ejemplo, existen registros así:
Abreu
Abréu

Pero no hay ningún registro duplicado tipo:
Abreu
Abreu

En este caso podrías trabajar con un indice normal (no unique) pero claro no tendràs ese control.

El control para evitar duplicidades se podria implementar en el procedimiento para agregar nuevos apellidos. Quiero decir que en lugar de hacer solo un INSERT INTO, se puede consultar si el apellido ya esta en la tabla y si esta no insertarlo. Puede que sea lento pero entiendo que no serà una cosa habitual ya que ya partes de un número grande de apellidos. Pero claro eso se deberá hacer con un cotejamiento que te distinga las distintas formas del apellido.

[ricardobit]

Gracias a los dos por vuestras respuestas. Voy a ponerme manos a la obra, a ver que tal. :)