[SOLUCIONADO] Cómo indexar la base de datos

[mblascog]

Buenas,
Me encuentro que las consultas que hago a la base de datos son muy lentas.
No sé como afrontar el problema y me gustaría saber vuestra opinión.
En primer lugar decir que se trata de una pequeña base de datos para mostrar unos resultados en una web.
Está compuesta por unas 14 tablas. 13 de ellas son muy pequeñas, contienen 3 o 4 campos con un máximo de 600 registros. Y la otra, tiene 30 campos y unos 800 registros.
Creo que por el tamaño de la base de datos el acceso debería de ser más rápido.

Me preguntaba si añadiendo índices a la base de datos podría obtener más velocidad.
Cuál es la vuestra opinión? Gracias

[ccliment]

Hola buenas,

Lo primero que creo que deberías hacer es darnos la estructura de tablas y las SQL que estás lanzando. Si haces un EXPLAIN SELECT .... obtendrás información muy valiosa que nos la puedes reenviar para que te ayudemos.

Con esas tablas tan pequeñas el problema es fijo de índices.

Saludos.

[gnzsoloyo]

Los índices no se agregan a la base de datos sino a las tablas, aclaremos. Y no necesariamente te darán rapidez.
Como ya te han dicho, sin ver qué consultas haces, no podemos saber a priori por que son lentas.
Puede deberse a muchas causas.

Postea algunas que te resulten especialmente lentas y veremos.

[mblascog]

Buenas,
Aclaro, cometí error a la hora de exponer el tema. Los índices se añaden a las tablas, no a las bases de datos.

Código MySQL:

SELECT un.*, es.titulo, es.descripcion, po.nombre_poblacion, ti.nombre_tipo_ca, 
op.operacion_tipo_ca FROM t_un as un, t_ca as es, t_poblacion as po, t_tipo as ti, t_operacion as op 
WHERE un.id_piso = es.id_piso AND un.poblacion = po.id_poblacion AND un.tipo = ti.id_tipo AND 
un.operacion = op.id_operacion AND un.referencia IN ((SELECT first FROM t_destacados WHERE id_cat='3'), 
(SELECT second FROM t_destacados WHERE id_cat='3'), (SELECT third FROM t_destacados WHERE 
id_cat='3'), (SELECT forth FROM t_destacados WHERE id_cat='3'), (SELECT fifth FROM t_destacados WHERE 
id_cat='3'), (SELECT sixth FROM t_destacados WHERE id_cat='3')) LIMIT 0, 6
 

Código MySQL:

SELECT un.*, po.nombre_poblacion, ti.nombre_tipo_ca, op.operacion_tipo_ca FROM 
t_un as un, t_poblacion as po, t_tipo as ti, t_operacion as op, t_familia AS fm WHERE un.poblacion = 
po.id_poblacion AND un.tipo = ti.id_tipo AND un.operacion = op.id_operacion AND un.operacion = 4 AND 
un.operacion = fm.id_familia AND un.familia = 3 AND ti.id_tipo = '3' ORDER BY un.id_piso DESC
 

Gracias

[mblascog]

Y la estructura de la base de datos es esta. No conozco otra manera de obtener la estructura que no sea con mysqldump:

Código MySQL:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Table structure for table `t_administradores`
--
 
DROP TABLE IF EXISTS `t_administradores`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_administradores` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `user` varchar(20) NOT NULL,
  `pass` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pass` (`pass`),
  KEY `user` (`user`),
  KEY `pass_2` (`pass`),
  KEY `id` (`id`),
  KEY `user_2` (`user`),
  KEY `id_2` (`id`),
  KEY `user_3` (`user`),
  KEY `pass_3` (`pass`),
  KEY `id_3` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_ca`
--
 
DROP TABLE IF EXISTS `t_ca`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_ca` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_piso` varchar(25) DEFAULT NULL,
  `titulo` varchar(255) DEFAULT NULL,
  `descripcion` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=498936 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_de`
--
 
DROP TABLE IF EXISTS `t_de`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_de` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_piso` varchar(25) DEFAULT NULL,
  `titulo` varchar(255) DEFAULT NULL,
  `descripcion` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=498115 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_destacados`
--
 
DROP TABLE IF EXISTS `t_destacados`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_destacados` (
  `id_cat` int(1) NOT NULL,
  `first` varchar(5) NOT NULL,
  `second` varchar(5) NOT NULL,
  `third` varchar(5) NOT NULL,
  `forth` varchar(5) NOT NULL,
  `fifth` varchar(5) NOT NULL,
  `sixth` varchar(5) NOT NULL,
  PRIMARY KEY (`id_cat`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_en`
--
 
DROP TABLE IF EXISTS `t_en`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_en` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_piso` varchar(25) DEFAULT NULL,
  `titulo` varchar(255) DEFAULT NULL,
  `descripcion` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=485708 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_es`
--
 
DROP TABLE IF EXISTS `t_es`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_es` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_piso` varchar(25) DEFAULT NULL,
  `titulo` varchar(255) DEFAULT NULL,
  `descripcion` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=377500 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_familia`
--
 
DROP TABLE IF EXISTS `t_familia`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_familia` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_familia` varchar(255) DEFAULT NULL,
  `tot_venda` int(11) DEFAULT NULL,
  `tot_lloguer` int(11) DEFAULT NULL,
  `nombre` varchar(255) DEFAULT NULL,
  `nombre_familia_es` varchar(255) DEFAULT NULL,
  `nombre_familia_ca` varchar(255) DEFAULT NULL,
  `nombre_familia_fr` varchar(255) DEFAULT NULL,
  `nombre_familia_en` varchar(255) DEFAULT NULL,
  `nombre_familia_de` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6870 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_fr`
--
 
DROP TABLE IF EXISTS `t_fr`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_fr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_piso` varchar(25) DEFAULT NULL,
  `titulo` varchar(255) DEFAULT NULL,
  `descripcion` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=498120 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_operacion`
--
 
DROP TABLE IF EXISTS `t_operacion`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_operacion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_operacion` varchar(255) DEFAULT NULL,
  `operacion_tipo_es` varchar(255) DEFAULT NULL,
  `operacion_tipo_ca` varchar(255) DEFAULT NULL,
  `operacion_tipo_fr` varchar(255) DEFAULT NULL,
  `operacion_tipo_en` varchar(255) DEFAULT NULL,
  `operacion_tipo_de` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2481 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_poblacion`
--
 
DROP TABLE IF EXISTS `t_poblacion`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_poblacion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_poblacion` varchar(255) DEFAULT NULL,
  `nombre_poblacion` varchar(255) DEFAULT NULL,
  `id_provincia` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=28634 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_servei`
--
 
DROP TABLE IF EXISTS `t_servei`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_servei` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titulo_es` varchar(255) DEFAULT NULL,
  `titulo_ca` varchar(255) DEFAULT NULL,
  `titulo_fr` varchar(255) DEFAULT NULL,
  `titulo_en` varchar(255) DEFAULT NULL,
  `titulo_de` varchar(255) DEFAULT NULL,
  `descripcion_es` text,
  `descripcion_ca` text,
  `descripcion_fr` text,
  `descripcion_en` text,
  `descripcion_de` text,
  `orden` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=155 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_situacion`
--
 
DROP TABLE IF EXISTS `t_situacion`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_situacion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_situacion` varchar(255) DEFAULT NULL,
  `nombre_situacion` varchar(255) DEFAULT NULL,
  `id_poblacion` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=69337 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `t_tipo`
--
 
DROP TABLE IF EXISTS `t_tipo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_tipo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_tipo` varchar(255) DEFAULT NULL,
  `id_familia_tipo` varchar(255) DEFAULT NULL,
  `nombre_tipo_es` varchar(255) DEFAULT NULL,
  `nombre_tipo_ca` varchar(255) DEFAULT NULL,
  `nombre_tipo_fr` varchar(255) DEFAULT NULL,
  `nombre_tipo_en` varchar(255) DEFAULT NULL,
  `nombre_tipo_de` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14669 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 

[/HIGHLIGHT]

[mblascog]

Código MySQL:

--
-- Table structure for table `t_un`
--
 
DROP TABLE IF EXISTS `t_un`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_un` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_piso` varchar(25) DEFAULT NULL,
  `referencia` varchar(25) DEFAULT NULL,
  `operacion` int(11) DEFAULT NULL,
  `tipo` varchar(255) DEFAULT NULL,
  `familia` varchar(255) DEFAULT NULL,
  `poblacion` varchar(255) DEFAULT NULL,
  `situacion1` varchar(255) DEFAULT NULL,
  `situacion2` varchar(255) DEFAULT NULL,
  `foto1` varchar(255) DEFAULT NULL,
  `foto2` varchar(255) DEFAULT NULL,
  `foto3` varchar(255) DEFAULT NULL,
  `foto4` varchar(255) DEFAULT NULL,
  `foto5` varchar(255) DEFAULT NULL,
  `foto6` varchar(255) DEFAULT NULL,
  `precio` decimal(10,2) DEFAULT NULL,
  `habitaciones` int(11) DEFAULT NULL,
  `metros` decimal(10,0) DEFAULT NULL,
  `estado` int(11) DEFAULT NULL,
  `fechaHoraRegistro` datetime DEFAULT NULL,
  `CodigoPostal` varchar(255) DEFAULT NULL,
  `DireccionInmueble` varchar(255) DEFAULT NULL,
  `AbreviacionTipoCalle` varchar(255) DEFAULT NULL,
  `NombreCalleInmueble` varchar(255) DEFAULT NULL,
  `AbreviacionTipoNumero` varchar(255) DEFAULT NULL,
  `NumeroCalleInmueble` varchar(255) DEFAULT NULL,
  `Parametrizable1` int(11) DEFAULT NULL,
  `google_lat` varchar(255) DEFAULT NULL,
  `google_lng` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foto2` (`foto2`)
) ENGINE=MyISAM AUTO_INCREMENT=325928 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
-- Dump completed on 2014-09-30 23:23:55
 

[gnzsoloyo]

Mira, todavía estoy revisando tu problema (también trabajo todo el día), pero en esencia el problema tiene varios frentes:
1) Las consultas son ineficientes por falta de indices en algunos casos, excesos de indices en otros, uso de subconsultas en el WHERE (ineficientes mayoritariamente), y utilizacion de JOINs imnplícitos (la coma), en lugar de explicitos.
2) Las tablas no están normalizadas, ni siquiera están en la 1FN, lo que obliga a realizar consultas completamente antiperformánticas. Si se las redujese, tendrías muchísimos menos problemas.
3) La selección de tipos de datos y sus longutudes es equivocada en muchos casos. El error de los tipos peude causar que los índices que se puedan crear no aporten suficiente optimización.
4) Los campos de TEXT deberían ser usados en índices de tipo FULLTEXT, aprovechando que usas tablas MySAM.
5) El uso de ORDER BY conspira contra el resultado ya que es una de las cláusulas asesinas de performance, en especial con consultas que devuelven muchos registros.
6) otros...

Es evidente, por algunos detalles, que estás basandote en documentación obsoleta (algo muy comun en la web), ya que te comento que un VARCHAR no mtiene actualmente un límitte de 255 caracteres. Eso ocurría hace casi diez años atrás, pero no ahora. El límite está bastante más arriba, y ese cambio podría ayudar a evitar usar columnas de tipo TEXT, que exigen al motor de base de datos otro tipo de recursos.
Esta noche termino de mirar bien lo que has posteado pero desde ya hasy bastante que se puede mejorar.

[mblascog]

Gracias gnzsoloyo, me has hundido!

[gnzsoloyo]

Tranquilo... todo es solucionable, aunque requerirá algo de esfuerzo y paciencia.

[mblascog]

Gnzsoloyo, estoy analizando la base de datos.
1.- las tablas tienen solamente un índice, el de la clave principal, ninguno más, por lo que no entiendo que digas exceso de índices.
2.- dices que no está en 1FN, porqué existe algun campo vació?
3.- puedo repasar el tipo de campos
4.- "Los campos de TEXT deberían ser usados en índices de tipo FULLTEXT, aprovechando que usas tablas MySAM.", no entiendo a qué campos te refieres, los varchar?
5.- En cuanto al order? Pero si quieres los campos ordenados, cómo lo haces?

Muchas gracias

[gnzsoloyo]

1.- las tablas tienen solamente un índice, el de la clave principal, ninguno más, por lo que no entiendo que digas exceso de índices.

Código MySQL:

CREATE TABLE `t_administradores` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `user` varchar(20) NOT NULL,
  `pass` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pass` (`pass`),
  KEY `user` (`user`),
  KEY `pass_2` (`pass`),
  KEY `id` (`id`),
  KEY `user_2` (`user`),
  KEY `id_2` (`id`),
  KEY `user_3` (`user`),
  KEY `pass_3` (`pass`),
  KEY `id_3` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
 

Cada indicacion d KEY crea un índice...

2.- dices que no está en 1FN, porqué existe algun campo vació?

Un campo vacío no implica que no esté en 1FN. Entre otras cosas tienes más de un campo sobre el mismo dominio, por ejemplo:

Código MySQL:

CREATE TABLE `t_un` (
....
  `foto1` varchar(255) DEFAULT NULL,
  `foto2` varchar(255) DEFAULT NULL,
  `foto3` varchar(255) DEFAULT NULL,
  `foto4` varchar(255) DEFAULT NULL,
  `foto5` varchar(255) DEFAULT NULL,
  `foto6` varchar(255) DEFAULT NULL,
...
) ENGINE=MyISAM AUTO_INCREMENT=325928 DEFAULT CHARSET=latin1;
 

y eso está mal diseñado. Eso se debe haer con una tabla adicional, y no agregando campos, ni aún cuando los uses todos.
Nota: eso no es lo único que define la 1FN.

4.- "Los campos de TEXT deberían ser usados en índices de tipo FULLTEXT, aprovechando que usas tablas MySAM.", no entiendo a qué campos te refieres, los varchar?

A este caso, por ejemplo:

Código MySQL:

CREATE TABLE `t_en` (
  ...
  `descripcion` text,
  ...
) ENGINE=MyISAM;
 

Y NO, no confundo TEXT con VARCHAR...

5.- En cuanto al order? Pero si quieres los campos ordenados, cómo lo haces?

No dije que no se pueda usar, sino que cuando la consulta devuelve muchos registros el ORDER BY, así como el GROUP BY, son asesinos de performance. En esos casos lo primero que hay que ver es un buen EXPLAIN de la consulta, para asegurrse que sea óptima.

[mblascog]

Gracias gnzsoloyo,
Si vamos a analizar la estructura de la base de datos he procedido a eliminar todas aquellas tablas y campos que ya no se usan.
La tabla Administradores, la que tenía definidos tantos índices ya no existe, por lo tanto, punto 1 solucionado.
En cuanto al punto 2. Estoy de acuerdo con lo que dices, pero en este caso tampoco lo veo tan mal. Te diré el porqué y díme si estás de acuerdo. Esta base de datos se alimenta de un programa de gestión. Sólo se vuelcan los datos que interesan para la web. Este programa obliga a introducir les 6 fotos, por lo que siendo fijo, no veo la necesidad de crear una tabla adicional. Además la consulta es más fácil con una sola tabla.
No te enfades, ya sé que no confundes Text con Varchar, pero trato de entenderte, por este motivo repito con mis palabras para asegurarme
Las consultas que se hacen devuelven muy pocos registros, como mucho, en el peor de los casos, 40 o 50. Y deben salir ordenados según petición. Es que no conozco otra forma de ordenar.

[mblascog]

Buenas, sigo con el tema.
Una consulta, de las más complejas que hay en la web y que tarda mucho es la siguiente:

Código MySQL:

SELECT un.*, es.titulo, es.descripcion, po.nombre_poblacion, ti.nombre_tipo_ca, op.operacion_tipo_ca 
FROM t_un as un, t_ca as es, t_poblacion as po, t_tipo as ti, t_operacion as op WHERE un.id_piso = es.id_piso AND un.poblacion = po.id_poblacion AND un.tipo = ti.id_tipo AND un.operacion = op.id_operacion AND un.referencia 
IN ((SELECT first FROM t_destacados WHERE id_cat='3'), (SELECT second FROM t_destacados WHERE 
id_cat='3'), (SELECT third FROM t_destacados WHERE id_cat='3'), (SELECT forth FROM t_destacados WHERE 
id_cat='3'), (SELECT fifth FROM t_destacados WHERE id_cat='3'), (SELECT sixth FROM t_destacados WHERE 
id_cat='3')) LIMIT 0, 6
 

He probado con estas dos, que:

Código MySQL:

SELECT un.*, es.titulo, es.descripcion, po.nombre_poblacion, ti.nombre_tipo_".$idioma.", op.operacion_tipo_".$idioma.
FROM t_un as un, t_".$idioma." as es, t_poblacion as po, t_tipo as ti, t_operacion as op
WHERE un.id_piso = es.id_piso AND un.poblacion = po.id_poblacion AND un.tipo = ti.id_tipo 
AND un.operacion = op.id_operacion AND (un.referencia = ".$oportunitats['1']." 
or un.referencia = ".$oportunitats['2']." or un.referencia = ".$oportunitats['3']." 
or un.referencia = ".$oportunitats['4']." 
or un.referencia = ".$oportunitats['5']." 
or un.referencia = ".$oportunitats['6'].")
 

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

Pero el tiempo de respuesta es más o menos igual. Alguna sugerencia o manera de optimizar esta consulta?

Gracias

[gnzsoloyo]

Una consulta, de las más complejas que hay en la web y que tarda mucho es la siguiente:

La consulta es "compleja" porque está basada en una tabla sin normalizar. De allí que necesites hacer cosas normalmente inexistentes.
Para mantener la base sin tocar, y tratar de hacerla más eficiente, hay que forzar el uso de ciertas funciones que no serían necesarias en tablas bien definidas.
Esto sería un acercamiento:

Código MySQL:

SELECT 
    un . *,
    es.titulo,
    es.descripcion,
    po.nombre_poblacion,
    ti.nombre_tipo_ca,
    op.operacion_tipo_ca
FROM
    t_un as un,
    t_ca as es,
    t_poblacion as po,
    t_tipo as ti,
    t_operacion as op
WHERE
    un.id_piso = es.id_piso
        AND un.poblacion = po.id_poblacion
        AND un.tipo = ti.id_tipo
        AND un.operacion = op.id_operacion
        AND FIND_IN_SET((SELECT CONCAT(first, ', ', second, ', ', third, ', ', forth, ', ', fifth, ', ', sixth)
                FROM t_destacados
                WHERE id_cat = 3), un.referencia )
 
LIMIT 0 , 6
 

REspecto a la segunda, el sólo hecho de que estés definiendo los nombres de las tablas dinámicamente de esa forma, me habla de problemas de diseño mayores aún. Por otro lado, no nos sirve que postees código de programación (cosa que desde ya es OFF TOPIC), sino que en todo caso necesitaríamos que nos postearas la sentencia como finalmente queda creada, es decir lo que MySQL realmente recibe.

[mblascog]

Buenas gnzsoloyo,
No entiendo porqué me dices que se trata de una tabla no normalizada, partiendo de esta consulta:
Existe la tabla t_un, con el campo referencia y otros más
Existe la tabla t_destacados, que contiene las referencias de los 6 inmuebles destacados, y por el orden que el propio nombre del campo indica. Existen siempre las 6 referencias

Tampoco entiendo porqué dices que estoy definiendo los nombres de las tablas dinámicamente. Lo que hago es primero obtener las 6 referencias de la tabla destacados y luego montar el where en la segunda tabla, ya con los valores (a no ser que lo digas por lo del idioma, es que el campo es, por ejemplo, nombre_tipo_ca si está en catalán, nombre_tipo_es si está en español, ....)

Gracias

[gnzsoloyo]

Que he existan siempre las seis referencias, y que el orden sea uno determinado, no implica que deba haber seis campos en una tabla dedicados a ello.
Algo que creo que ya mencioné es que la 1FN claramente expresa que no deben existir campos diferentes que posean el mismo dominio, y en este caso cada campo contiene la referencia a un inmueble. Para el caso es el mismo dominio, que los seis VALORES puedan ser diferentes es un tema procedimental, no de bases de datos, desde la óptica del diseño de datos, estas poniendo campos que comparten dominios (son inmuebles), y eso está mal.
Si esa referencia se pudiese sacar de la tabla y pasarla a otra tabla, la consulta se volvería mucho más simple en ciertas cosas.

[mblascog]

Gracias por responder.
Es decir, tu crees que la consulta sería mucho más rápida si en lugar de existir esto:
-tabla1.referencia
-tabla2 con los siguientes campos:
- id_cat
- first
- second
- third
- forth
- fifth
- sixth

Existiera esto:
-tabla1.referencia
-y la tabla2:
- id_cat
- orden
- referencia

Y la consulta debería ser así:

Código MySQL:

SELECT tabla1.* 
FROM tabla2, tabla1 
WHERE tabla2.id_cat = '3' AND
        tabla2.referencia = tabla1.referencia
ORDER BY orden
 

Acabo de probarlo gnzsoloyo. Rapidísimo!!!!

No marco el tema como solucionado, porqué ahora voy a intentar modificar la base de datos y no sé si me surgirá algun problema

[gnzsoloyo]

Como notas:
- Trata de no usar JOIN implícitos (la coma), sino explícitos. Son más eficientes y con menos tendencia a errores por nombres de campos. En especial con tablas InnoDB son más eficientes ya que ese tipo de tablas tiene indices especiales para la administración de las FK.
- Es conveniente que te acostumbres a usar alias cuando usas JOINs, para facilitar la legibilidad de los códigos SQL.
- Los valores numéricos no deben ser comparados como cadenas. Hacer eso obliga a MySQL a realizar conversiones implícitas que pueden terminar pagándose con performance cuando son miles de registros.
- El uso de comparaciones como cadenas de texto para facilitar la programación, también, esconde problemas de programación cuando el valor a comparar no llega a la sentencia, pero queda oculto al escribirse como una cadena vacía. Recordar que una cadena vacía no es NULL...

Usando INNER JOIN (JOIN explícito) quedaría mas o menos así:

Código MySQL:

SELECT T1.* 
FROM tabla2 T1 INNER JOIN tabla1 T1 ON T2.referencia = T1.referencia
WHERE tabla2.id_cat = '3' 
ORDER BY orden
 

[mblascog]

Gracias gnzsoloyo. Tomo nota, en estos momentos estoy modificando la web para adaptarla al cambio de la base de datos.
Cuando acabe lo pruebo.

[mblascog]

Buenas, ya he hecho los cambios. Esta consulta funciona:

Código MySQL:

SELECT t_un . * 
FROM t_destacats
INNER JOIN t_un ON t_destacats.referencia = t_un.referencia
WHERE t_destacats.id_cat =3
ORDER BY t_destacats.ordre
 

Pero necesito campos de otras tablas y añadir alguna condición al WHERE y no me sale. Tendría que ser algo así, pero no funciona:

Código MySQL:

SELECT t_un.*, es.titulo, es.descripcion, po.nombre_poblacion, ti.nombre_tipo_ca, op.operacion_tipo_ca
FROM t_destacats, t_ca as es, t_poblacion as po, t_tipo as ti, t_operacion as op INNER JOIN t_un ON t_destacats.referencia = t_un.referencia
WHERE t_destacats.id_cat = 3 AND t_un.id_piso = es.id_piso AND t_un.poblacion = po.id_poblacion AND t_un.tipo = ti.id_tipo AND t_un.operacion = op.id_operacion
ORDER BY t_destacats.ordre
 

Da el error de t_destacats.referencia unknown.
No sé como hacerlo, pero creo que lo estoy haciendo muy mal. Gracias

[gnzsoloyo]

Primero, no mezcles JOINs implícitos (la coma) con explícitos. Los resultados tienden a ser erróneos o disparar errores de sintaxis o lógica inesperados y a veces difíciles de diagnosticar.
La query debería ser así, según lo que nos muestras:

Código MySQL:

SELECT 
    t_un . *,
    es.titulo,
    es.descripcion,
    po.nombre_poblacion,
    ti.nombre_tipo_ca,
    op.operacion_tipo_ca
FROM
    t_destacats d 
    INNER JOIN t_un U ON d.referencia = U.referencia
    INNER JOIN t_ca es ON U.id_piso = es.id_piso
    INNER JOIN t_poblacion po ON U.poblacion = po.id_poblacion
    INNER JOIN t_tipo ti ON U.tipo = ti.id_tipo
    INNER JOIN t_operacion op ON U.operacion = op.id_operacion
WHERE
    D.id_cat = 3
ORDER BY D.ordre
 

Da el error de t_destacats.referencia unknown.

Eso implica que la columna "referencia" no existe con ese nombre en la tabla "t_destacats". Sin ver el CREATE TABLE de esa tabla no puedo saber si es un error real, o es que hay un error de interpretación del parser.
POdrías postear el resultado de esto?:

Código MySQL:

SHOW CREATE TABLE t_destacats;
 

[mblascog]

Buenas,
He ejecutado la consulta, pero ya vuelvo a tener el problema.

t_destacats:

Código MySQL:

CREATE TABLE `t_destacats` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_cat` int(11) NOT NULL,
 `ordre` int(11) NOT NULL,
 `referencia` varchar(5) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ordre` (`ordre`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1
 

t_poblacion

Código MySQL:

CREATE TABLE `t_poblacion` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_poblacion` int(11) DEFAULT NULL,
 `nombre_poblacion` varchar(30) DEFAULT NULL,
 `id_provincia` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=29569 DEFAULT CHARSET=latin1
 

t_ca

Código MySQL:

CREATE TABLE `t_ca` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_piso` varchar(25) DEFAULT NULL,
 `titulo` varchar(120) DEFAULT NULL,
 `descripcion` text,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=510545 DEFAULT CHARSET=latin1
 

t_tipo
CREATE TABLE `t_tipo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_tipo` int(11) DEFAULT NULL,
`id_familia_tipo` int(11) DEFAULT NULL,
`nombre_tipo_es` varchar(50) DEFAULT NULL,
`nombre_tipo_ca` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=15060 DEFAULT CHARSET=latin1

t_operacion

Código MySQL:

CREATE TABLE `t_operacion` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_operacion` int(11) DEFAULT NULL,
 `operacion_tipo_es` varchar(50) DEFAULT NULL,
 `operacion_tipo_ca` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2553 DEFAULT CHARSET=latin1
 

Me he dado cuenta que al crear la tabla t_destacats, con la estructura que me sugeristes, se me ha creado como InnoDB, no como MyISAM. Intento cambiarla pero no me deja.
No sé qué es lo que exactamente hace que vaya todo tan lento.

Gracias

[mblascog]

Buenas,
He ido probando inner join a inner join, para ver cual era la que enlentecía la consulta. He observado que a partir del tercero iba muy lento, fuera cual fuera el orden.
Como las tablas sólo tenían la clave principal como índice, he añadido índices a todos los campos del inner join y va rapidísimo.

Muchas gracias.

[gnzsoloyo]

Es algo que puede ocurrir...
La lentitud de una consulta puede deberse a multiples factores, pero sustancialmente lo que más imapcta es la cardinalidad de las relaciones. Si una de las tablas tiene una selectividad demasiado baja (cada registor d eotra devuelve muchos de esa), es muy probable que una indexación de alguno de los campos de esa tabla mejore la performance, o bien que se pueda indicar condiciones que aumenten la selectividad de ese caso en especial
Alli los índices empiezan a tener más impacto.
Lo que suele convenir es verificar el plan de consultas de la query usada con EXPLAIN, para ver dónde está perdiendo performance:

Código MySQL:

EXPLAIN SELECT 
    t_un . *,
    es.titulo,
    es.descripcion,
    po.nombre_poblacion,
    ti.nombre_tipo_ca,
    op.operacion_tipo_ca
FROM
    t_destacats d 
    INNER JOIN t_un U ON d.referencia = U.referencia
    INNER JOIN t_ca es ON U.id_piso = es.id_piso
    INNER JOIN t_poblacion po ON U.poblacion = po.id_poblacion
    INNER JOIN t_tipo ti ON U.tipo = ti.id_tipo
    INNER JOIN t_operacion op ON U.operacion = op.id_operacion
WHERE
    D.id_cat = 3
ORDER BY D.ordre
 

[mblascog]

Gracias gnzsoloyo. Ayuda Explain ayuda a comprobar si tienes las tablas bien indexadas. Pero como interpretas donde ser pierde el performance?