Optimizacion de algoritmo o optimizacion de BD?

[reynierpm]

Hola a todos, me disculpais si la pregunta no pertenece a este Foro pero realmente no sabia si ponerla aca o en el foro de BD. Bueno el tema es el siguiente, 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?
Saludos

[loncho_rojas]

Soluciona primero tu problema de consultas en el foro de MYSQL o BD y luego vente para los de los array y demas reportes si aún no sabes como hacerlo.... escribe más corto y conciso, y a medida que te ayuden agregale detalles..

[portalmana]

Que tipos de datos guardas en las tablas o si puedes poner su estructura o un ejemplo.

Una idea para optimizar un poco y no tener que usar dos arrays y merge seria una union de la consulta mysql.

Código MySQL:

(SELECT * FROM tbl_blacklist) UNION (SELECT * FROM tbl_cmdatabase)
 

Tendrias que afinarla por si tienes mas campos y eso pero si coinciden creo que se mantiene uno de los valores y no los dos por lo cual eliminas el merge.

Saludos

[reynierpm]

Basicamente las tres tablas tienen la siguiente estructura:

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 ;

Los campos se repiten en las tres tablas

[portalmana]

y supongo que lo que no se debe repetir es el id_telefonica y numero.

[reynierpm]

Lo que no debe repetirse es solo el numero el id_telefonica si se puede repetir

[portalmana]

Código MySQL:

(SELECT number FROM tbl_blacklist) UNION (SELECT number FROM tbl_cmdatabase)
 

Con esta consulta ya obtendrías todos los números sin repetirse con lo cual no usas el merge.

Que contenido va a tener number, pues esta definido como varchar, pues si son solo números se puede hacer alguna vuelta para que pueda ser mas rápido.
Es posible definir number como campo numerico o no porque va a contener algun otro tipo de caracter.

Salduos

[reynierpm]

Puede ser definido como campo numerico, lo unico que se almacena alli es un numero que comienza por 0, digamos 04241805609, sin guiones ni nada raro, que me recomiendas?

[portalmana]

Esto definiendo el campo number como Numerico(bigint).
El problema quizás que perderemos el cero del principio, salvo que pusiéramos zerofill.

Como dices que el Excel trae hasta mas o menos 50.000 números, yo prefiero cargar los números de excel a un arreglo, pues este quizás no siga creciendo pero la base muy probablemente si.
Luego por las dudas y para no saturar el servidor partiría la búsqueda en las consultas tu podrías elegir en cuantos ciclos realizas el traspaso.

Por ejemplo si el excel tiene 50.000 lineas y dices en 10 ciclos, entonces tomaría
el valor cero del array que tiene cargado el excel que es el numero mas chico y el valor 4.999 y limitaría la consulta a ese rango.
Quedaría mas o menos así:

Código PHP:

(SELECT number FROM tbl_blacklist WHERE number BETWEEN valorMin AND valorMax) UNION (SELECT number FROM tbl_cmdatabase WHERE number BETWEEN valorMin AND valorMax) ORDER BY number
// Bueno puedes poner >< que debera ir mas rapido.
 

y haces mas o menos lo mismo que estas haciendo, verificar que cada numero del excel no este en el arreglo de resultados y repites para los ciclos que elegiste.

No se si mejorara la performance pero es una idea.
Lo otro es con procedimientos almacenados en la propia base mysql.

Saludos y suerte

[reynierpm]

Hmm no entendi esta parte

Por ejemplo si el excel tiene 50.000 lineas y dices en 10 ciclos, entonces tomaría el valor cero del array que tiene cargado el excel que es el numero mas chico y el valor 4.999 y limitaría la consulta a ese rango.

o sea para que limitar la consulta?

[portalmana]

lo que haria serian ciclos es decir
excel[0] = 15.464.656
excel[4999] = 17.565.656
el segundo ciclo lo realizo con
excel[5000] = 18.001.323
excel[9999] = 25.001.655

y así sucesivamente.
Con esto quizás obtenga arreglos de comparación mas chicos y pueda lograr mayor velocidad del script que es lo que estas buscando.
Aparte podria servirte en caso de servidor compartido para el manejo de memoria, que el script no se te corte por memoria si vas borrando los arreglos entre ciclo y ciclo, quedaria lo del tiempo.

Pero es solo una idea...