Optimizacion de BD

[reynierpm]

Tengo tres tablas en mi BD (ver imagen adjunta) llamadas: tbl_blacklist que contiene alrededor de 92 038 registros, tbl_cmdatabase que contiene alrededor de 45 742 registros y por ultimo tbl_ndatabase que contiene 194 074 registros. La tabla principal donde siempre inserto nuevos datos y consulto es la mas grande tbl_ndatabase. La forma de introducir los datos es leerlos de un fichero Excel que tiene una sola columna pero en ocasiones hasta mas de 50 000 filas. De por si leer un fichero Excel desde PHP es una tarea lenta (estoy usando PHPExcel). Para insertar un nuevo dato en la tabla tbl_ndatabase debo comprobar primero que no este en la tabla tbl_blacklist ni en la tabla tbl_cmdatabase. El algoritmo que hice para ello fue:

1. Realizar una consulta a la BD, obtener todos los registros de la tabla tbl_blacklist y ponerlos en un array
2. Realizar una consulta a la BD, obtener todos los registros de la tabla tbl_cmdatabase y ponerlos en un array
3. Hacer un array_merge para que me quede en uno solo
4. Por cada fila que encuentre en el Excel y usando la funcion in_array verifico que el valor no este y asi me da la seguridad de que al menos en esas dos tablas no va a estar.

Esto es super bien lento y no encuentro forma de hacerlo mas sencillo. Que me sugieren? Alguna idea? Debo cambiar el algoritmo o hacer algun tipo de optimizacion en la BD para que se ejecute mas rapido? Estoy realizando pruebas en una VM con CentOS 6.4 y 3GB de RAM no quiero ni ver que sucede si lo subo a mi hosting

Por otro lado y relacionado con este mismo problema, tengo otra situacion y es que debo mostrar para luego generar un fichero Excel los registros desde el 1 hasta el 194 074 avanzando en un valor que me entren en la aplicacion, digamos que entre 1000 entonces lo que estoy haciendo es generar una tabla HTML que comienza en 1 y aumenta de 1000 en 1000 de la siguiente forma:
Código:
1-1000
1001-2000
2001-3000
Y asi sucesivamente pero esto es tambien super lento y a veces demora hasta unos 3 min en generarse la pagina. Cuando le doy generar el Excel y quiero comenzar en 1001 a 2000 demora 10 min cosa que suena bien loco porque cualquier hosting da como maximo 60 seg o algo asi.

Todas las pruebas las hago en local, que me sugieren?

[quimfv]

Con la información que das sobre la base de datos no se puede decir nada...

Esas tres tablas què significan?
Son necesarias o se podrian unir en una sola y diferenciar los registros con el valor de un campo?
O podrias tener una tabla principal con TODOS los registros y sus datos comunes y los no comunes en tablas a parte referenciadas...
Si trabajas con una sola tabla y el dato que viene del excel es la PK o un indice unico no necesitas ver si ya esta... si esta no entrará de nuevo.... Leete esto http://stackoverflow.com/questions/5...ate-key-update


En cuanto a la tabla conoces el formato csv.... quizas seria mas rapido que generar una tabla HTML...

putcsv PHP 5 >= 5.1.0

http://www.cesarvalladares.com/wordp...ble-desde-php/

[reynierpm]

Gracias por la respuesta @quimfv te comento, no puedo unir las 3 tablas xq las cantidades registradas en cada una varía además de que a pesar de ser casi idénticas en estructura sus contenidos significan cosas diferentes. Este es el DDL de una de ellas (se repite en las otras dos):

Código:

CREATE TABLE IF NOT EXISTS `tbl_ndatabase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_telefonica` int(11) NOT NULL,
  `number` varchar(11) COLLATE utf8_spanish2_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci AUTO_INCREMENT=1 ;

Tomemos la tabla tbl_ndatabase como la tabla principal y la tabla donde la columna number no puede estar repetida, entonces lo que yo hago es, via PHP, leo de un fichero Excel (.xls aunque puedo llevarlo a .csv) cargo todo ese listado de números (almacenados en una sola columna del Excel pero que puede llegar hasta 50 000 o más filas) y por cada valor busco que no este en la tabla tbl_cmdatabase (comparo con la columna number de esa tabla) y tbl_blacklist (comparo con la columna number de esa tabla) y si el valor que tengo en la fila del Excel no esta en ninguna de las dos tablas entonces ejecuto el INSERT. Ya entiendes el proposito y lo que quiero optimizar?

Saludos

[quimfv]

Si entiendo lo que quieres y como lo estas haciendo... pero

No explicas el significado de las tablas....con lo que es dificil saber si el diseño es correcto.
No entiendo porque un campo llamado number esta definido como varchar, un tipo string.
No entiendo porque si number no se puede repetir no defines un indice unico sobre el.
No explicas si hay actualización de las otras tablas.
No dices si entre las tablas "secundarias" se pueden repetir valores.

Si la estructura de las tabla se repite, y no hay repeticiones ni entre las tablas "secundarias", no entiendo porque no te ha de servir esto

Código MySQL:

CREATE TABLE IF NOT EXISTS `tbl_principal` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_telefonica` int(11) NOT NULL,
  `number` int(11) NOT NULL,
  `tabla` ENUM (1,2,3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ind_number` (`number`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci AUTO_INCREMENT=1 ;
 

donde el campo tabla te diferencia entre los valores de number y facilmente con un indice unico puedes controlar que no se repitan los valores.

Código MySQL:

CREATE VIEW tbl_ndatabase as SELECT * FROM tbl_principal WHERE tabla=1;
CREATE VIEW tbl_blacklist as SELECT * FROM tbl_principal WHERE tabla=2;
CREATE VIEW tbl_cmdatabase as SELECT * FROM tbl_principal WHERE tabla=3;
 

Estas tres vistas te permitiran trabajar como si tuvieras las tablas originales.

Solo tienes que agregar a los inserts el valor de tabla para cada caso....y aplicar esto http://stackoverflow.com/questions/5...ate-key-update

[reynierpm]

Voy a responder a tus dudas y a comentar al respecto de la posible solución que me sugeriste:

No explicas el significado de las tablas....con lo que es dificil saber si el diseño es correcto.

Las tablas son básicamente para almecenar en una columna un numero de telefono que puede comenzar por 0 o no, por ejemplo 04241805609 y 4241805609 son números válidos y deberían poder almacenarse, es mas no estoy seguro de que el ID sea necesario ya que esos números nunca serán editados. Cuando más eliminados pero a mi aplicación puedo pasarle como parámetro el número en si y como será único pues no creo que haya problemas

No entiendo porque un campo llamado number esta definido como varchar, un tipo string.

Quizás porque este mal mi diseño y ahí necesito la opinión de personas más experimentadas que yo, no sabía que existia la opción ZEROFILL en el tipo de dato INT hasta ayer que leí al respecto, creo que la solución sería poner un INT de longitud 11 con la propiedad UNSIGNED ZEROFILL así aceptaría solo positivos y pues bueno cuando no aparezca el 0 lo llenaría de forma automática, si me equivoco me corriges

No entiendo porque si number no se puede repetir no defines un indice unico sobre el.

Al igual que la respuesta anterior, quizás por un mal diseño, ahorita lo pondre como un índice único tal como esta en la sugerencia que me dejas acá.

No explicas si hay actualización de las otras tablas.

Si, todas las tablas deben ser actualizadas por lo cual el poner un campo ENUM y tener una sola tabla no me serviría dado que los registros pueden ser variables entre tablas. Fijate esto que dije al principio: tbl_blacklist ~92 038 registros, tbl_cmdatabase ~45 742 registros, tbl_ndatabase ~194 074 registros ahi ves la diferencia de registros existentes en cada una de las tablas. Los 50 000 que comento que son extraídos del Excel irían a parar en la tabla tbl_ndatabase en caso de no estar en tbl_blacklist ni en tbl_cmdatabase

No dices si entre las tablas "secundarias" se pueden repetir valores.

No, es un requerimiento que los valores almacenados en tbl_database (tabla primaria y principal) no estén ni en la tabla tbl_cmdatabase ni en tbl_blacklist, quizas entre estas dos ultimas si existan valores repetidos pero eso no me importa tanto dado que la tabla donde siempre hago consultas y selecciono datos es de la primaria.

El enlace que dejaste parece estar muerto
Al tanto de comentarios
Saludos

[quimfv]

http://stackoverflow.com/questions/5...ate-key-update

El enlace correcto ya te lo pase en el primer post...

Si son numeros de telefono ya entiendo que uses varchar y NO, yo no usaria int con zerofil para completar zeros...

El enum podria ser 0 solo la tabla principal, 1 tabla secundaria 1, 2 tabla secundaria 2 y 3 las dos secundarias

En cuanto a la exlicación del numero de registros de cada tabla, no me contesta lo que te pregunto, supongamos que tienes

tbl_Principal
id number tabla
1 YYYYYYY 0
2 ZZZZZZ 1
3 XXXXXX 2
4 ZZZZZZ 3

CREATE VIEW tbl_ndatabase as SELECT id,number FROM tbl_principal;

tbl_ndatabase
id number
1 YYYYYYY
2 ZZZZZZ
3 XXXXXX
4 ZZZZZZ

CREATE VIEW tbl_blacklist as SELECT id,number FROM tbl_principal WHERE tabla=1 OR tabla=3;

tbl_blacklist
id number
2 ZZZZZZ
4 ZZZZZZ

CREATE VIEW tbl_cmdatabase as SELECT id,number FROM tbl_principal WHERE tabla=2 OR tabla=3;

tbl_cmdatabase
id number
3 XXXXXX
4 ZZZZZZ

Tengan los registros que tengan... lo ves? Solo con que uses el indice unico mas INGNORE de la principal ya controlas las repeticiones

[reynierpm]

Hola, si ahorita veo lo que me tratas de explicar! Voy a probar y te comento como me fue
Saludos y gracias