tabla en BDD gigantesca y consulta interminable

derkenuke · #1 (**permalink**) 05/04/2007, 19:39

Hola

Tengo una base de datos en mysql en la que opero por medio de PHP, y por, cómo no, phpmyadmin.

En ella existe una sóla tabla experimental, que la he hecho gigantesca. Unos datos:

Código:

+-------------------------------+
|	ESPACIO UTILIZADO	|
+-------+-----------------------+
|Tipo	|	Uso		|
|Datos	|	4,096 	MB	|
|Índice	|	1,943 	MB	|
|Total	|	6,039 	MB	|
+-------+-----------------------+

Vamos, unos 5,89gb!. Tiene 82.596.931 registros

y esta estructura:

Código:

+-------------------------------------------------------+
|			ESTRUCTURA			|
+-------+---------------+-------------------------------+
|CAMPO	| 	TIPO	|		OTROS		|
+-------+---------------+-------------------------------+
|campo1	| bigint(9)	| no nulo, único, primario	|
|campo2	| varchar(8)	| no nulo			|
|campo3	| varchar(32)	| no nulo			|
+-------+-----------------------------------------------+

El caso es que para hacer una consuta tipo

Código:

select campo1,campo2,campo3 from tabla where campo1=34

no tarda casi nada, enseguida devuelve un resultado. Creo que es por ser campo1 el campo primario.

Para hacer una consulta con la cláusula where y el campo campo2 es cuando se tira una infinidad:

Código:

select campo1,campo2,campo3 from tabla where campo2="paco"

He puesto el límite de tiempo de ejecución para un php en 600 segundos (10 minutos) y todavía salta.

En phpmyadmin cuando hago una consulta de ese tipo también es interminable, no así con campo1.

¿Alguien ha trabajado con tablas tan gigantescas?
¿Cual puede ser la posible solución al problema? He pensado que podría dividir la tabla en varias, pero no se cómo acerlo ya que con phpmyadmin no puedo exportar esa cantidad de registros; he probado con 1millón y ha tardado lo menos 15 minutos... y eso tendría que hacerlo 83 veces... es inviable.

¿Soluciones?

BrujoNic · #2 (**permalink**) 05/04/2007, 21:59

¿qué pasa si declaras el campo2 como llave foránea? Al estar indexado, debería ser más rápida la respuesta.

derkenuke · #3 (**permalink**) 06/04/2007, 07:13

Sí, lo he pensado esta noche. Lo pruebo, pero tendré que dejar esto trabajando una horita por lo menos para ese pequeño cambio.

Aún así la consulta que quiero hacer es para recoger todos los registros en los que campo3 sea el mismo, eso sí que tardaría, y no puedo poner campo3 como clave porque se repiten registros..

Gracias por contestar!

derkenuke · #4 (**permalink**) 06/04/2007, 08:28

Sí, he cambiado el campo2 a clave, y ahora busca muy rápido (instantáneo) en cuando en el where incluyo campo1 ó campo2:

Código:

where campo1=12					<- INSTANTÁNEO
where campo2='pepe'				<- INSTANTÁNEO
where campo3='900150983cd24fb0d6963f7d28e17f72'	<- LENTÍSIMO

¿Hay alguna solución para ésto? Si divido la tabla (siempre que pudiera) seguirá siendo lento por tener que buscar en varias tablas...

BrujoNic · #5 (**permalink**) 06/04/2007, 10:21

En cualquier motor de BD existe las llaves principales y foráneas, no todo puede ser llave.

Siempre va a ver circunstancias en las cuales las consultas se van a tener que hacer sobre un campo no llave y por consiguiente, los tiempos de respuesta se van a degradar.

Es por eso que existe la normalización de BD para que evitar en lo posible, ese tipo de situaciones, te aconsejo leer esto Función de la sección de Base de Datos, para que te des una idea de lo que te estoy diciendo.

Saludos.

derkenuke · #6 (**permalink**) 06/04/2007, 14:06

Pero no puedo hacer el campo3 llave porque no todos sus registros son diferentes, entonces la búsqueda siempre será lenta si depende de campo3..

No veo en qué me puede servir la Normalización en este caso... Ahora tengo únicamente campo1, campo2 y campo3; pero estoy intentando eliminar campo1, ya que la clave primaria ahora es campo2 y campo1 ya no es necesario.

Es una tabla con una estructura muy sencilla, no es nada complejo. El único aspecto que tiene complicación es el tamaño...

BrujoNic · #7 (**permalink**) 07/04/2007, 00:11

Si lo que necesitas es una búsqueda por campo2 y campo3, tan sencillo que declares una llave compuesta que contenga ambos campos.

derkenuke · #8 (**permalink**) 07/04/2007, 18:06

Sí, lo he probado con una tabla con 1millón de registros y parece ser que va considerablemente más rápido. Quizás sea esa la solución.

Muchas gracias por la ayuda BrujoNic!

Saludos.

deadlykyo · #9 (**permalink**) 07/04/2007, 18:46

Holas derkenuke, creo que se olvidaron mencionar un concepto importante y es el uso de indices, aqui les dejo algo extractado del manual de mysql:

Cita:

The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, then this is at least 100 times faster than reading sequentially

el crear una llave primaria o foranea no es la solución adecuada desde mi punto de vista, para eso se crearon los indices que se encargan de mejorar los tiempos de respuesta de los SELECTS, lo que tendrias que hacer es ver si necesitas crear un indice combinado para ambas columnas o un indice para cada una de ellas, puedes consultar mas la documentacion de mysql para conocer mas sobre el uso de indices, saludos, cya
http://dev.mysql.com/doc/refman/5.0/es/indexes.html

BrujoNic · #10 (**permalink**) 08/04/2007, 00:33

Bueno, estoy de acuerdo...

Para que sea más claro, dejemos el concepto de llave primaria y llave(s) secundaria(s).