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.