Problema select tabla 45.000 registros

[israel965]

Hola,

Actualmente estoy trabajando en un proyecto en php, el cuál obtiene la información de base de datos de archivos access(.mdb).

No tenemos mayores problemas con este proceso, creamos las tablas con los campos que consideramos son los más óptimos para cada caso etc.

El problema es que tenemos una tabla, la cuál contiene 45.000 registros con 30 campos, la mayoría varchar de 255, y, al hacerle una select * o una select c1, c2, c3, c4, dependiendo del servidor dónde la lancemos tarda más o menos.

Por ejemplo, en mi máquina local(localhost) tarda unos 12 segundos(es demasiado), en otro server con un sistema parecido pero con windows server 2003 tarda 1, o 2, en cambio en otro server con un servicio de hosting tarda entre 10 y 30 segundos.

Lo bueno de todo esto es que tenemos alguna tabla con más o menos los mismos registros que tiran en 0, es decir, trabajan de forma correcta, así que no es problema del server así a mayores, pero hay algo que se nos escapa y ni yo ni mis compañeros somos capaces de entender.

Agradecería un poco de colaboración a ver si alguien se ha visto en un caso similar y nos puede echar un cable.

Nuestras tablas trabajan todas con innodb, por si es un dato interesante.

No hemos tocado nada de la configuración de mysql.

Saludos.

[quimfv]

de archivos access(.mdb).

y

Nuestras tablas trabajan todas con innodb

en que quedamos?

más o menos los mismos registros que tiran en 0

mismo tipo de campos? Mismo contenido? Todo ello afecta a la respuesta.

Manda la estructura de la tabla en cuestión y la o las consultas que demoran y alguien te podrá decir algo.

[gnzsoloyo]

Actualmente estoy trabajando en un proyecto en php, el cuál obtiene la información de base de datos de archivos access(.mdb).

Ponte de acuerdo: o trabajas con Access, o con MySQL. Los hibridos nunca funcionan bien.

Si quieres consejos para Access, muevo tu post al foro de BBDD General.

Por otro lado:

al hacerle una select * o una select c1, c2, c3, c4, dependiendo del servidor dónde la lancemos tarda más o menos.

Eso es de cajón. Toda consulta con "*"va a tardar siempre más que una donde defines qué campos quieres, sea porque sobrecargas la conexion de datos-basura, como porque en el DBMS usas más bloques de datos en memoria de los necesarios, y además saturas innecesariamente el buffer de consultas.

Pero empecemos por el principio... ¿Access o MySQL?

[israel965]

Iniciado por gnzsoloyo

Ponte de acuerdo: o trabajas con Access, o con MySQL. Los hibridos nunca funcionan bien.

Si quieres consejos para Access, muevo tu post al foro de BBDD General.

Por otro lado:

Eso es de cajón. Toda consulta con "*"va a tardar siempre más que una donde defines qué campos quieres, sea porque sobrecargas la conexion de datos-basura, como porque en el DBMS usas más bloques de datos en memoria de los necesarios, y además saturas innecesariamente el buffer de consultas.

Pero empecemos por el principio... ¿Access o MySQL?

Hola y gracias por responder,

No me he explicado bien, hacemos una importación de access a mysql, es decir, cogemos la información del access y hacemos n inserts en mysql.

Por cada campo que añadimos a la consulta, select c1, c2, c3, c4 tarda prácticamente un segundo más.

Este es el esquema de la tabla en cuestión:

Código MySQL:

CREATE TABLE `actividades` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(10) unsigned NOT NULL,
  `c2` varchar(45) DEFAULT NULL,
  `c3` varchar(255) DEFAULT NULL,
  `c4` varchar(255) DEFAULT NULL,
  `c5` datetime DEFAULT NULL,
  `c6` varchar(45) DEFAULT NULL,
  `c7` varchar(45) DEFAULT NULL,
  `c8` varchar(45) DEFAULT NULL,
  `c9` varchar(255) DEFAULT NULL,
  `municipi` varchar(45) DEFAULT NULL,
  `Comarca` varchar(255) DEFAULT NULL,
  `territori` varchar(255) DEFAULT NULL,
  `lloc` varchar(255) DEFAULT NULL,
  `LlocResumit` text,
  `NOTES` text,
  `c10` varchar(255) DEFAULT NULL,
  `c11` varchar(255) DEFAULT NULL,
  `c12` varchar(255) DEFAULT NULL,
  `c13` varchar(255) DEFAULT NULL,
  `c14` varchar(255) DEFAULT NULL,
  `c15` varchar(255) DEFAULT NULL,
  `c16` varchar(255) DEFAULT NULL,
  `c17` varchar(255) DEFAULT NULL,
  `c18` varchar(255) DEFAULT NULL,
  `fk_ident` int(11) DEFAULT NULL,
  `c19` text,
  `c20` varchar(255) DEFAULT NULL,
  `c21` varchar(255) DEFAULT NULL,
  `c22` varchar(45) DEFAULT NULL,
  `c23` varchar(120) DEFAULT NULL,
  `c24` float(10,6) DEFAULT NULL,
  `c25` float(10,6) DEFAULT NULL,
  `c26` float(10,6) DEFAULT NULL,
  `c27` float(10,6) DEFAULT NULL,
  `c28` varchar(45) DEFAULT NULL,
  `indret` varchar(255) DEFAULT NULL,
  `id_poblacio` int(10) unsigned DEFAULT NULL,
  `id_comarca` int(10) unsigned DEFAULT NULL,
  `id_territori` int(10) unsigned DEFAULT NULL,
  `latitudPoblacio` float(10,6) DEFAULT NULL,
  `longitudPoblacio` float(10,6) DEFAULT NULL,
  `c29` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=45133 DEFAULT CHARSET=utf8;
 

No podemos hacer un select de menos campos, los utilizamos todos, de todas formas, no hacemos un select *

Gracias y saludos.

[israel965]

Gracias por la respuesta,

Es exactamente la misma base de datos en 3 servidores distintos, uno en mi local(va bastante lento), otra que va relativamente bien(2 segndos un select *) en la misma red con un windows server 2003 y otra en un servicio de hosting que tenemos contratado que va tremendamente mal.

De nuevo gracias por vuestro tiempo.

Saludos.

[gnzsoloyo]

Más allá de las diferencias de configuración y recursos que tenga cada servidor (no alcanza con lo que dices como para dar una opinión), esa tabla la veo con bastantes problemas.
Como mínimo, no veo que tenga índices definidos, por lo que cualquier consulta basada en los campos VARCHAR será forzosamente ineficeinte.
Por otro lado, veo que no está normalizada, o al menos estos segmentos me sugieren que hay desnormalización, cosa que siemrpe se paga con performance:

Código MySQL:

`c2` varchar(45) DEFAULT NULL,
  `c3` varchar(255) DEFAULT NULL,
  `c4` varchar(255) DEFAULT NULL,
  `c5` datetime DEFAULT NULL,
  `c6` varchar(45) DEFAULT NULL,
  `c7` varchar(45) DEFAULT NULL,
  `c8` varchar(45) DEFAULT NULL,
  `c9` varchar(255) DEFAULT NULL,
...
  `LlocResumit` text,
  `NOTES` text,
...
  `c10` varchar(255) DEFAULT NULL,
  `c11` varchar(255) DEFAULT NULL,
  `c12` varchar(255) DEFAULT NULL,
  `c13` varchar(255) DEFAULT NULL,
  `c14` varchar(255) DEFAULT NULL,
  `c15` varchar(255) DEFAULT NULL,
  `c16` varchar(255) DEFAULT NULL,
  `c17` varchar(255) DEFAULT NULL,
  `c18` varchar(255) DEFAULT NULL,
...
  `c19` text,
...
  `c20` varchar(255) DEFAULT NULL,
  `c21` varchar(255) DEFAULT NULL,
  `c22` varchar(45) DEFAULT NULL,
  `c23` varchar(120) DEFAULT NULL,
  `c24` float(10,6) DEFAULT NULL,
  `c25` float(10,6) DEFAULT NULL,
  `c26` float(10,6) DEFAULT NULL,
  `c27` float(10,6) DEFAULT NULL,
  `c28` varchar(45) DEFAULT NULL,
...
  `c29` int(10) DEFAULT NULL,
 

Incluyo los campos TEXT, porque el sólo hecho de que sean nulables, me sugiere que son campos opcionales, y en consecuencia no deberían estar en la tabla base. En todo caso deberían estar en una tabla auxiliar.

El hecho de que los datos provengan de una única tabla de Access, no es óbice para no normalizar.
Yo he tenido la oportunidad de trabajar con datos provenientes de cuatro tablas de datos externas, desnormalizada, que luego de recuperada y normalizada la metíamos en 39 tablas diferentes. Y eso lo hacíamos para aumentar la eficiencia de la base.
Todo el tiempo de proceso que se gaste normalizando se gana luego en las consultas.

Finalmente, ¿Qué tipo de consultas hacen? ¿Las hacen con WHEREs sobre condiciones con esos campos VARCHAR? ¿REalizan JOINs de algún tipo?
¿Puedes mostrar algunos ejemplos?

[israel965]

Hola de nuevo,

El tema de la normalización es complejo con esta base de datos, aunque estoy completamente de acuerdo, (El hecho de que los datos provengan de una única tabla de Access, no es óbice para no normalizar).

El problema es que aunque suene extraño, no tenemos campos que sean not null en nuestra tabla, todos tienen algún valor que no nos provee datos.

Aún así podríamos mejorar su estructura y sacar campos que se repiten como localizaciones(comarcas, territorios etc).

La base de datos access es administrada por n personas y cada una lo hace a su criterio, para nosotros es incluso complicada su importación por muchos motivos.

Nosotros creamos la siguiente vista que hace una union all contra otra tabla

Código MySQL:

CREATE VIEW `vwtac` AS select 
`totact`.`tipus` AS `id_tipus`,
`totact`.`id_unic` AS `id`,
`totact`.`DIA` AS `data_inici`,
`totact`.`DIA` AS `data_fi`,
`totact`.`id_poblacio` AS `id_poblacio`,
`totact`.`id_comarca` AS `id_comarca`,
`totact`.`id_territori` AS `id_territori`,
`totact`.`poblacio` AS `poblacio`,
`totact`.`Comarca` AS `comarca`,
`totact`.`territori` AS `territori`,
`totact`.`municipi` AS `municipi`,
`totact`.`cbla1` AS `cbl1`,
`totact`.`cbla2` AS `cbl2`,
`totact`.`cbla3` AS `cbl3`,
`totact`.`cbla4` AS `cbl4`,
`totact`.`cbla5` AS `cbl5`,
`totact`.`cbla6` AS `cbl6`,
`totact`.`cbla7` AS `cbl7`,
0 AS `id_indret`,
`totact`.`latitud` AS `lat`,
`totact`.`longitud` AS `lng`,
0 AS `idind`,
`totact`.`latitudmaltemps` AS `lat_mal_temps`,
`totact`.`longitudmaltemps` AS `lng_mal_temps`,
`totact`.`latitudPoblacio` AS `latitudPoblacio`,
`totact`.`longitudPoblacio` AS `longitudPoblacio`,
`totact`.`LinkPrograma` AS `link_programa`,
`totact`.`fkf` AS `programa`,
`totact`.`HMATI` AS `hora1`,
`totact`.`HTARDA` AS `hora2`,
`totact`.`HNIT` AS `hora3`,
'' AS `hora4`,
`totact`.`descripcio` AS `desctipus`,
`totact`.`lloc` AS `lloc`,
`totact`.`llocre` AS `llocre`,
`totact`.`sipl` AS `sipl`,
`totact`.`splre` AS `splre`,
`totact`.`idcl1` AS `idcl1` from `totac` `totact` 
 
union all select 
 
999 AS `id_tipus`,
`tblcu`.`id` AS `id`,
`tblcu`.`data_inici` AS `data_inici`,
`tblcu`.`data_acabament` AS `data_fi`,
`tblpob`.`id` AS `id_poblacio`,
`tblcom`.`id` AS `id_comarca`,
`tblter`.`id` AS `id_territori`,
`tblpob`.`poblacio` AS `poblacio`,
`tblcom`.`comarca` AS `comarca`,
`tblter`.`territori` AS `territori`,
'' AS `municipi`,
'' AS `cbl1`,
'' AS `cbl2`,
'' AS `cbl3`,
'' AS `cbl4`,
'' AS `cbl5`,
'' AS `cbl6`,
'' AS `cbl7`,
0 AS `id_indret`,
NULL AS `lat`,
NULL AS `lng`,
NULL AS `idind`,
NULL AS `lat_mal_temps`,
NULL AS `lng_mal_temps`,
NULL AS `latitudPoblacio`,
NULL AS `longitudPoblacio`,
NULL AS `link_programa`,
0 AS `programa`,
`tblcu`.`hini1` AS `hora1`,
`tblcu`.`hfi1` AS `hora2`,
`tblcu`.`hini2` AS `hora3`,
`tblcu`.`hfi2` AS `hora4`,
'' AS `desctipus`,
'' AS `lloc`,
'' AS `llocre`,
'' AS `sipl`,
'' AS `splre`,
'' AS `idcl1` 
from (((`tblcu` 
join `tblpob` on((`tblcu`.`id_poblacio` = `tblpob`.`id`))) 
join `tblcom` on((`tblpob`.`id_comarca` = `tblcom`.`id`))) 
join `tblter` on((`tblcom`.`id_territori` = `tblter`.`id`)));
 

Una vez hecha la vista, como la aplicación soporta varios idiomas tenemos tablas de traducción contra la que hacemos su respectiva join, si esto no es suficiente, tenemos un buscador que genera consultas de este tipo:

Código MySQL:

(programa != 0 OR link_programa IS NOT NULL) AND data_inici BETWEEN '2010-08-06' AND '2015-08-31' AND id_tipus IN(1,5,4) AND vw.id_comarca IN(38,60) AND vw.id_territori IN(9,3,7) AND vw.id_poblacio IN(1037,1020) AND idcl1IN(225,145,224,300,192,335)
 

Finalmente con todo eso generamos una consulta que realiza una paginación mostrando 20, 30, 50 o 100 resultados a demanda del usuario.

Sí tenemos índices, son sobre los que hacemos las búsquedas, aunque no los hemos añadido, de todos modos, como puedes ver, hacemos búsquedas sobre campos de tipo entero y fechas.

Gracias por vuestra ayuda.

[israel965]

También decir que llevamos una pequeña estrategia de caché para consultas repetitivas, pero con esta información no puede ser aplicada porqué varia continuamente.