Aumentar rendimiento en busqueda

[TEKILA]

Hola, os pido ayuda porque ya no se que probar. Estoy realizando búsquedas en una base de datos (solo búsquedas, no realizo UPDATE) y mis tablas son de tipo InnoDB.
Pues entre ellas hay una que es muy lenta. Os lo mostrare resumidamente.
Tengo dos tablas, una llamada titulos con 10 columnas y unas 20.000 filas y otra llamada autor con 3 columnas y 40.000 filas. De las 3 columnas de autor una es una referencia a la columna private key de titulos.

titulos:
---------------------------
id | col_1 | col_2 | ...
1 a d
2 b e
3 c f

autor:
----------------------------
id | nombre | id_titulo
1 pedro 5
2 juan jose 9
3 carlos 6

En autor he creado el siguiente indice:
Index name - Field name - Index type
mi_indice1 name(25) Normal

En titulos he creado el siguiente indice:
Index name - Field name - Index type
mi_indice2 id Unique

Bien pues con todo esto, cuando realizo la siguiente busqueda:

Código:

SELECT * FROM titulos WHERE id = ANY
(SELECT id_titulo FROM autor WHERE nombre LIKE "%unaPalabraCualquiera%");

Mi búsqueda es demasiado lenta y ya no se que hacer para mejorar su rapidez sin cambiar de tipo de tabla. Cualquier sugerencia me vale por tonta que os parezca. Por cierto, las tablas tienen el texto en UTF-8, por si os ayuda el dato.
Gracias desde ya.

[jurena]

Para eso no necesitas hacer subconsulta alguna.
1) Indexa primero el campo nombre.
2) Lanza luego la siguiente consulta:

Código sql:

SELECT * FROM titulos INNER JOIN autor ON autor.id_titulo = titulo.id WHERE autor.nombre LIKE "%unaPalabraCualquiera%";
 

y observarás una notable mejora.

[TEKILA]

Gracias por contestar jurena.
He probado como me has dicho pero apenas he notado cambio. Además no lo he dicho por simplificar, pero hago una búsqueda alternativa, es decir, que si no hay ningún resultado en la tabla autor busca en una llamada alias, por lo que no se si me compensa hacerlo de ese modo por un poco mas de rendimiento pero perder la posibilidad de buscar en 2 tablas.
Pongo el código original, pero tampoco importa mucho (ya que los EXIST los he comprobado aparte y son instantáneos), es solo para haceros una idea.

Código:

SELECT * FROM titulo WHERE EXISTS(SELECT id_titulo FROM autor WHERE
 nombre LIKE "%unaPalabra%") AND id = ANY(SELECT id_titulo FROM autor WHERE
 nombre LIKE "%unaPalabra%") UNION SELECT * FROM titulo WHERE NOT
 EXISTS(SELECT id_titulo FROM autor WHERE nombre LIKE "%unaPalabra%") AND
 no = ANY(SELECT id_titulo FROM alias WHERE alias LIKE "%unaPalabra%");

Tal vez haya metido la pata con los índices? yo el índice de la columna nombre lo cree así:
CREATE INDEX miIndice1 ON autor(nombre(25));

Decir que nombre es una columna de tipo TEXT que por lo que comprobado como máximo tiene 91 caracteres. Seria mejor otro tipo de campo o variar 25 por un numero mayor?.

Pregunto un poco por preguntar, porque es que ya no se como rebajar el tiempo.
Me sirve cualquier semi-idea.

[jurena]

Tekila,
explícanos algo mejor el alcance de tu consulta, pues, por lo que veo, afecta siempre a dos tablas que están relacionadas. Por otra parte, tal vez te compensaría un índice de tipo FULL TEXT. Pero antes dinos qué es lo que buscas con algún ejemplo.

[TEKILA]

Creo que FULL TEXT solo vale para tablas MyISAM, no?
http://www.forosdelweb.com/f86/full-text-usando-innodb-476725/

Lo que hace es buscar en la tabla autor, y solamente en caso de no encontar ninguna coincidencia, buscara en la tabla alias.

LAs dos sentencias están unidas mediante un UNION y dentro de cada una hay un EXISTS como condición en el WHERE, que lo que hace es comprobar si hay resultados en la tabla autor. De este modo solo se muestran resultados de una tabla, ya que los EXISTS son iguales, pero uno pose un NOT que lo niega (el segundo concretamente).

No se si me explica bien...

[jurena]

Iniciado por TEKILA

Creo que FULL TEXT solo vale para tablas MyISAM, no?
http://www.forosdelweb.com/f86/full-...innodb-476725/

Lo que hace es buscar en la tabla autor, y solamente en caso de no encontar ninguna coincidencia, buscara en la tabla alias.

LAs dos sentencias están unidas mediante un UNION y dentro de cada una hay un EXISTS como condición en el WHERE, que lo que hace es comprobar si hay resultados en la tabla autor. De este modo solo se muestran resultados de una tabla, ya que los EXISTS son iguales, pero uno pose un NOT que lo niega (el segundo concretamente).

No se si me explica bien...

Cierto, de momento FULL TEXT es sólo para motores MyIsam.
La sintaxis para el índice de nombre, creo que la tienes bien, aunque también puedes utilizar esto:
ALTER TABLE nombretabla ADD INDEX nombreindice(nombrecampo(40)), aunque no sé por qué limitas la ordenación a un número determinado de caracteres si los buscas en todo el campo nombre y el campo no es muy grande. ¿Cuál es el tamaño de ese campo nombre?

Pero lo que no entiendo es la lógica.
Dinos cuáles son los campos de esas tres tablas (por cierto no sé para qué es la tabla alias), su naturaleza y relaciones, y describe la lógica de búsqueda paso a paso: por ej., quiero que la consulta me busque en la tabla títulos todos los títulos de libro, con sus autores (procedentes de la tabla autores), títulos que contengan la palabra x en el campo título o en los campos, y caso de no encontrar ninguno, ... , etc.
Quizás de esa manera podamos ayudarte mejor.

[tute666]

Es irrelevante el indice, ya que al hacer un like "%texto%" al tener un comodin previo al texto buscado, no hay manera de que el motor pueda recorrer el indice.

Soluciones viables, serian: myisam con full text index, pero seria sacrificar foreign keys.
Sino, existen soluciones de indexado "caseras" como la que implementa doctrine: http://www.doctrine-project.org/documentation/manual/1_0/en/searching#introduction (inglés)
o en su defecto, investigar soluciones como Sphinx: www.sphinxsearch.com/ (inglés)