Manejar con php una base de datos grandisima

[mafima]

Buenso dias, habia hecho un sistema para una comunidad, todo fucnionaba muy bien pero despues de que tuvimos mas de 12000 usuarios el servidor dedicado empezo a fallar y a atascarse

Se que el problema esta es en la forma en que programé el sistema y estructure la base de datos, porque en el mismo servidor tenemos otra comunidad ( porgramada por una empresa ) con mas de 13000 usuarios y un chat en HTML ( sin ajax ) y esta comunidad no atasca el server.

una de los factores determinantes era que el servidor se demoraba mucho haciendo cualquier query entonces se iban acumulando las conexiones hasta que se producia un error TOO MANY CONNECTIONS, ( todo esto lo investigue siguiendo los LOGS del server y haciendo seguimiento continuo via TELNET y SSH )

Ahora he tomado la decision de reestructurar la base de datos y hacer de nuevo los QUERY y quiero que me den consejos de que debo tener en cuenta, ( TODO lo necesario para que no se me bloquee el servidor).

les explico un poco la estructura de las tablas:

hay un tabla de usuarios_genenerales.
ID_usuario
usuario
clave
edad
sexo

Hay una tabla de usuarios_intercambio
ID_usuario_intercambio
ID_usuario
Fecha_de_intercambio
pais_intercambio

hay una tabla de idioma
ID_usuario_idioma
ID_usuario
idioma_enseña
indioma_aprende


mas o menos es asi, entonces el susuario se registra en la tabla de usuarios geneales y ya elije si quiere ser de intercambio o de idiomas o de ambos.
las tres tablas se relacionan con el campo ID_usuario.

Una de mis preguntas es:

1. Será mejor juntar todas las tablas en la primer tabla usuarios_generales ?
2. es mejor tener un solo campo clave como index?
3. los ID los pongo como autoincrementables o mejor los inremento yo manualmente.

Cuales pueden ser los factores para que mis consultas sean rapidas?

De antemano muchas gracias. acepto caulquier sugerencia.

[GatorV]

Tema trasladado a MySQL.

[jurena]

Te diré lo que yo haría. Mis sugerencias no son las de un técnico, pero quizás puedan empezar a orientarte hasta tanto te llegan recomendaciones basadas en conocimientos técnicos. Imagino que estamos hablando de tablas MyIsam, aunque tendrías que preguntar si para tu caso no sería mejor usar InnoDB y gestionar las relaciones de manera más segura.
Para empezar yo incluso añadiría más tablas, una para idiomas y otra de países;

hay un tabla de usuarios_genenerales.
ID_usuario (Primary Key) INT autoincrementable Not Null
usuario
clave
edad
sexo
IDpaisprocedencia INT (index) relacionado con el id de la tabla países

Hay una tabla de usuarios_intercambio
ID_usuario_intercambio (Primary Key) autoincrementable Not Null
ID_usuario INT (index) relacionado como Foreign Key con el id de la tabla usuarios_generales
Fecha_de_intercambio campo DATE
idpais_intercambio INT (index) relacionado como Foreign Key con el id de la tabla paises

hay una tabla de idioma
ID_usuario_idioma (Primary Key) INT autoincrementable Not Null
ID_usuario INT (index)
IDidioma_enseña INT (index) relacionado como Foreign Key con el id de la tabla idiomas
IDidioma_aprende INT (index) relacionado como Foreign Key con el id de la tabla idiomas

tabla de países
IDpaises (Primary Key) INT autoincrementable Not Null
país

tabla de idiomas
IDidioma (Primary Key) INT autoincrementable Not Null
idioma

1. Será mejor juntar todas las tablas en la primer tabla usuarios_generales ? No. Es mejor hacer una correcta estructura relacional, basada en el uso de relaciones entre campos numéricos y un correcto aprovechamiento de los índices.2. es mejor tener un solo campo clave como index? Debe haber un sólo campo Primary Key que es un índice por naturaleza, pero tú debes indexar otros campos para que las relaciones funcionen de manera más rápida y eficaz.
3. los ID los pongo como autoincrementables o mejor los inremento yo manualmente. Ponlos como autoincrementables

De todas formas luego también el modo de hacer las consultas cambiará. Por otra parte, habría que ver si el problema no es sólo de la base de datos y su gestión, sino de la programación mediante PHP, ASP, etc.

Esto es para empezar..., pero, como te digo, si vas a empezar de cero, espera a que alguien con más conocimientos te aconseje un tipo de tabla para el uso que le vas a dar.

[mafima]

Gracias jurena,
El problema es que antes tenia tambien la tablas paises como dices ( eso si no le habia puesdo Clave foranea) y cuando lo tenia asi entonces andaba mas lento.
Me toco pasar la tabla de los paises a un archivo y meter todo dentro de un array poniendo como INDICE del array el ID del pais.

Es sumamente extraño, y ya que mensionas que el problema puede estar tambien en la programación, quizas tengas experiencia en como optimizar a travez del codigo.

Espero más comentarios que me ayuden con este problema, muchas gracias a todos.
PD: uso PHP para programar.

[jurena]

Iniciado por mafima

Gracias jurena,
El problema es que antes tenia tambien la tablas paises como dices ( eso si no le habia puesdo Clave foranea) y cuando lo tenia asi entonces andaba mas lento.
Me toco pasar la tabla de los paises a un archivo y meter todo dentro de un array poniendo como INDICE del array el ID del pais.
.

Si no lo tenías como clave foránea y tampoco lo habías indexado, ahí está el problema. Para estos casos, yo no usaría un array de PHP. Te aseguro que yo tengo tablas algo más complejas unidas con inner join con PK y FK con sus índices y las consultas van bastante rápido. En mi opinión, la clave está, como te he dicho, en establecer bien las relaciones, en poner los índices adecuados y en hacer las consultas más apropiadas, para lo que el explain nos ayuda bastante.
Un ejemplo de un mal uso: tú pones en idiomas
español
francés
ruso
inglés
incluso cuando indexas ese campo la velocidad con que es procesado es mucho menor que si tuvieras números indexados
1
4
7
8
y eso por no hablar sobre cuando haces group by, order, etc.
Y eso se nota a la larga y mucho...
Muestra alguna de tus consultas y la estructura de las tablas, y seguro que habrá quien te ayude a acelerar las consultas.
En mi opinión, hacer el trabajo bien en MySQL; luego te ocuparas de sacar el mejor rendimiento a PHP (pide ayuda en el foro PHP), aunque probablemente alguien con más conocimientos que yo sobre PHP me corrija y me "dé un buen tirón de orejas" refutando con argumentos esta opinión mía propia de un inexperto.

[mafima]

Hombre pues seguro sabes mas que yo, porque ni uso explai ni PK ni FK ( es primera vez que los oigo, voy a poner la verdadera estructura de la tabla de usuarios:

Agradezco muchisimo tu ayuda, la verdad es que me siento frustrado.

Código PHP:

  CREATE TABLE `usuarios` (
  `ID_usuario` int(11) NOT NULL auto_increment,
  `usuario` varchar(50) default NULL,
  `nombre` varchar(30) default NULL,
  `genero` tinyint(1) default NULL,
  `email` varchar(50) default NULL,
  `clave` varchar(15) default NULL,
  `region` varchar(6) default NULL,
  `pais` varchar(6) default NULL,
  `msn` varchar(50) default NULL,
  `skype` varchar(50) default NULL,
  `icq` varchar(50) default NULL,
  `yahoo` varchar(50) default NULL,
  `fecha_nacimiento` date default NULL,
  `ocupacion` text,
  `intereses` text,
  `ocupacion_en` text,
  `biografia` text,
  `biografia_en` text,
  `foto` varchar(150) default NULL,
  `libro` varchar(255) default NULL,
  `pelicula` varchar(255) default NULL,
  `educador` tinyint(1) NOT NULL default '0',
  `empleador` tinyint(1) NOT NULL default '0',
  `casero` tinyint(1) NOT NULL default '0',
  `familia` tinyint(1) NOT NULL default '0',
  `estudiante` tinyint(1) NOT NULL default '0',
  `internacional` tinyint(1) NOT NULL default '0',
  `ver_correo` tinyint(1) NOT NULL default '1',
  `tmp_id` int(11) default NULL,
  `tmp_tabla` varchar(60) default NULL,
  `tmp_fecha` datetime default NULL,
  `fecha_entro` datetime default '1910-04-04 00:00:00',
  `ID_region` smallint(4) NOT NULL default '0',
  `ID_country` smallint(3) NOT NULL default '0',
  `confirmacion` int(11) default '0',
  `confirmo` tinyint(1) default '0',
  `ciudad` varchar(250) default NULL,
  `fecha_ultimo_ingreso` datetime default NULL,
  `busqueda_general` text,
  `busqueda_language` text,
  `busqueda_study` text,
  `busqueda_family` text,
  `busqueda_housing` text,
  `preferencia_contacto` tinyint(1) NOT NULL default '4',
  PRIMARY KEY  (`ID_usuario`,`ID_region`,`ID_country`)
) ENGINE=MyISAM AUTO_INCREMENT=15011 DEFAULT CHARSET=utf8;  
   

( son muchas columnas, y estan como primary KEY `ID_usuario`,`ID_region`,`ID_country`)

en los campos primary KEY no pongo un valor en texto sino un numero ( asi lo etngo ahora y se demora mucho al hacer una consulta relacional)

ahora otra de las tablas:

Código PHP:

  CREATE TABLE `students_abroad` (
  `id` int(7) NOT NULL auto_increment,
  `pass` varchar(6) character set latin1 default '',
  `name` varchar(100) character set latin1 default '',
  `age` tinyint(2) default NULL,
  `sex` varchar(20) character set latin1 default NULL,
  `country` varchar(2) default NULL,
  `email` varchar(50) character set latin1 default NULL,
  `province` varchar(30) default NULL,
  `destination` varchar(2) default '',
  `region_code` varchar(2) default '',
  `length_stay` varchar(30) character set latin1 default NULL,
  `date_arrival` varchar(30) character set latin1 default NULL,
  `comments` longtext character set latin1,
  `date` varchar(20) character set latin1 default '',
  `ID_usuario` int(11) NOT NULL default '0',
  `comments_es` text,
  `activo` tinyint(1) default NULL,
  `ID_pais_destino` smallint(3) NOT NULL default '0',
  `ID_region_destino` smallint(4) NOT NULL default '0',
  `ciudad_destino` varchar(255) default NULL,
  PRIMARY KEY  (`id`,`ID_pais_destino`,`ID_region_destino`,`ID_usuario`)
) ENGINE=MyISAM AUTO_INCREMENT=5049 DEFAULT CHARSET=utf8;  
   

y una consulta donde relaciono ambas tablas:

Código PHP:

  SELECT students_abroad.ID_pais_destino,students_abroad.ID_region_destino,students_abroad.comments_es,students_abroad.date_arrival, students_abroad.length_stay, students_abroad.ciudad_destino,DATE_FORMAT(usuarios.fecha_ultimo_ingreso,'%d/%m/%Y') as fecha_ultimo_ingreso,DATE_FORMAT(usuarios.fecha_entro,'%d/%m/%Y') as fecha_entro, usuarios.foto,students_abroad.ID_usuario,usuarios.usuario,usuarios.genero,usuarios.fecha_nacimiento,usuarios.ID_country,usuarios.ID_region, regions.region_name, usuarios.ciudad,(SELECT region_name FROM regions WHERE ID_region=usuarios.ID_region) As region_origen FROM students_abroad INNER JOIN usuarios INNER JOIN regions ON students_abroad.ID_usuario=usuarios.ID_usuario AND students_abroad.ID_region_destino=regions.ID_region WHERE activo=1 GROUP BY usuarios.ID_usuario ORDER BY case when foto is not null THEN usuarios.ID_usuario END DESC, ID_usuario  
   

saco la lista de los registrs en la tabla studing_broad y saco datos del usuario de la tabla general y lueog los ordeno primero los que tienen foto. ( esta consulta va lentisima por que la tabla de usuarios tiene mas de 12000 registros y la otra tiene casi 6000

[jurena]

Yo observo algunos problemas, pero tampoco estoy muy seguro de lo que voy a decirte.

Veo algunas cosas raras (tablas poco partidas, subconsultas que parecen innecesarias, tablas ausentes, índices extraños, no uso de LIMIT en la consulta, relación poco visible), pero pero para ayudarte necesitaría más datos sobre la estructura lógica de tu base: qué es la tabla usuarios, para qué la tabla students_abroad, qué datos introduces y qué quieres sacar, cuál es la relación entre las tablas. Tal vez bastaría con que nos dijeras eso...

[mafima]

Ok esxplico un poco.

Es una comunidad de intyercambio estudiantil, entonces el usuario se registra y puede elegir una ovarias modalidades de intercambio:

Study_Abroad: intercambio de estudiantes de pais a pais.
Study_language: intercambio de idioma, los estudiantes no se desplazan sino que aprenden cada uno del lenguage que el otro quiere enseñar a traves de internet.
host_family: espara familias anfitrionas, que quieren recibir estudiantes de otras partes del mundo.

entonces un usuario se inscribe como usuario general ( en la tabla usuarios ) y yaelije cual tipo de intercambio quiere o si los quiere todos o solo dos.

es por eso que la tabla usuarios tiene tanta informacion.

quice partir las tablas porque me parecio que cuando fuera a mostrar por ejemplo la lista de study_abroad era mas facil buscar entre los 6000 usuarios que quieren study_abroad a buscar entre los 12000 que son usuarios generales.

Agradezco mucho tu ayuda, eres practicamente quien se ha encargado de ayudarme con esto que me tiene tana gobiado.

[mafima]

por cierto no uso el LIMIT en esa consulta por que tengo que sacar los datos de la paginacion.

[gnzsoloyo]

Así como se ve, las tablas en cuestión tiene una enorme falta de normalización.
La primera, Usuarios, puede partirse en al menos cinco tablas mejorando la eficiencia del modelo y de las consultas. La segunda, de seguro puede dividirse por lo menos en tres.
Mi sugerencia es que profundices en el modelo E-R y Estudies más profundamente el tema normalización.

[mafima]

Ok, ahora mismo estoy estoy buscando en google lo de normalizacion y el modelo E-R que mencionas, porque la verdad es que apenas y me vengo a enterar que existe este modelo.

Mientras tanto aprovecho para preguntar, porque se debe dividir la tabla en varias, no se supone que si en una cosnulta voy a mostrar todos los datos de un usuario será mejor que esten en una misma tabla y asi tenga que hacer menos inner join?

agradezco su ayuda.

[mafima]

porque partirias la tabla usuarios en 4 tablas mas, veo que en esta tabla los campos siempre dependen del ID del usuario. ( que es uan calve primaria )

por ejemplo tampoco veo que hayan atributos o campos que no son calves primarias que dependan de otros campos que tampoco lo son. ( lo que me hace penar que esta acorde con el 3 nivel de normalizacion )

Aunque apenas y estoy aprendiendo esto de los niveles y de estructurar la base de datos ya que lo que se es empiricamente.

muchas gracias

[gnzsoloyo]

Acá hay alguna orientación básica:
Modelo Entidad-Relación.
http://www.desarrolloweb.com/articul...-relacion.html

[Genetix]

aquí tienes un link que habla sobre normalizacion
http://www.mysql-hispano.org/page.php?id=16&pag=1

[mafima]

ok, ya lei el manual de mysql hispano y tambien le breve articulo de desarrolloweb
ambos me ayudanm a comprender mejor el asunto, sin embargo encuentro dificultas al intentar aplicarlo.

Me pueden ayudar ustedes a saber como debe quedar mi tabla usuarios para que este normalizada y adecuado a un correcto modelo E-R.

estoy seguro que con un ejemplo concreto me aclaro mas, desde luego les haré preguntas abusando de su generosidad.

De antemano muchas gracias.

por cierto tambien lei la primer parte de un "manual" de mysql (http://mysql.conclase.net ) y ahora estoy lleno de mucha información pero no se como aplicarla a mi problema ni como ponerlo en practica

[Genetix]

Mira este ejemplo
http://www.mysql-hispano.org/page.php?id=16&pag=3

Luego puedes usar un modelador de base de datos como http://www.fabforce.net/dbdesigner4/

Saludos!

[mafima]

Bueno ya he visto mucho de esos ejemplos ( porque em lei todo ese manual y otros tantos que habian en esa web.

pero aun asi no entiendo por que gnzsoloyo dice que debo dividir la tabla de usuarios en mas tablas, yo veo que todos los campos depende unicamente del campo ID_usuario ( excepto el campo region que si depende del pais ).

de nuevo muchas gracias

[Genetix]

Al parecer no entiendes muy bien los conceptos de normalización
ejemplo:

`educador`
`empleador`
`casero`
`familia`
`estudiante`

imagino que están haciendo referencia al estado o tipo de usuario, si es así esos datos son candidatos para estar en otra tabla, ejemplo tipo_usuario, y así puedes sacar región, país, biografía (ya que lo trabajas en 2 idiomas), etc

De esa manera vas normalizando tus datos, y por lo que veo tienes muchos datos que separar.

Trata de entender el concepto.

Si no normalizas las tablas tu sistema no va ser escalable y el rendimiento no va ser optimo
Saludos

[Gerifaltus]

Iniciado por Genetix

Al parecer no entiendes muy bien los conceptos de normalización
ejemplo:

imagino que están haciendo referencia al estado o tipo de usuario, si es así esos datos son candidatos para estar en otra tabla, ejemplo tipo_usuario, y así puedes sacar región, país, biografía (ya que lo trabajas en 2 idiomas), etc

De esa manera vas normalizando tus datos, y por lo que veo tienes muchos datos que separar.

Trata de entender el concepto.

Si no normalizas las tablas tu sistema no va ser escalable y el rendimiento no va ser optimo
Saludos

Lo que indica Genetix es verdad, debes normalizar tus tablas, la tabla de usuarios es muy grande, no la revisé a pronfundidad, pero con el solo hecho de verla de un vistazo me doy cuenta que debebes separar esa tabla en tres o más tablas.

Algo que aprendí en cuanto a base de datos es que no debes mezclar "entidades" en una sola tabla, una entidad es como si fuera una persona, cosa u objeto y que de él se desprenden otras caracteristicas (campos) propios ó inherentes a ese objeto, por lo tanto si logras identificar un objeto que tiene sus propias caracteristicas entonces ese objeto debe ir en otra tabla. Lo que te comento es una forma de normalizar una bd.

Hay otra forma un poco más lógica y conlleva a hacerte preguntas tales como (por ejemplo en tu tabla indicas campos para msn, yahoo,icq, skype):

¿1 usuario puede tener varios correos (haciendo referencia a que puede tener msn, yahoo, icq, etc)?, si es así entonces puede haber otra tabla llamada correos relacionada por el idUsuario, de esta manera eliminas 4 campos de la tabla usuarios.

OK, esta es mi humilde opinión, Saludos