Duda sobre indexación de tablas

[[DARK_RULO]]

Hola a tod@s!

Bueno, resulta que el día de hoy suspendieron mi sitio por abuso de recursos. Al parecer las conexiones a la base de datos se salieron un poco de la raya y por esto se tomó esa acción.

Al contactar a soporte técnico, se me recomendó indexar las tablas sql (mi base de datos es mysql), esto para optimizar su desempeño.

Y bueno, como soy novato en esto de la programación con php + mysql, no sé específicamente a que se refiere "indexar". Se que consiste en crear un índice en las tablas para facilitar la búsqueda de datos, pero no sé cómo debe de realizarse. También sé que al indexar una tabla, crece el tamaño de la bd.

Por ejemplo, tengo una tabla con los siguientes campos:

Código:

id
titulo
descripción
autor

Y tengo queries por ejemplo:

Código PHP:

  SELECT * FROM tabla WHERE id = id  
   

¿En qué consistiría indexar esa tabla para optimizar su rendimiento?

Saludos y gracias de antemano.

[pragone]

Un indice es una forma de indicar a MySQL que vas a realizar búsquedas por un campo particular para que a la hora de ejecutar la consulta puedas ubicar los datos con más rapidez.

En tu caso, el id probablemente sea un campo único auto_increment (por lo menos en definición). Si no es el caso, entonces te basta con crear un índice en el campo id... mira en: http://dev.mysql.com/doc/refman/5.0/...ate-index.html

[[DARK_RULO]]

Iniciado por pragone

En tu caso, el id probablemente sea un campo único auto_increment (por lo menos en definición). Si no es el caso, entonces te basta con crear un índice en el campo id

En efecto, el campo ID de mi tabla es auto_increment. Mencionas que si no fuera auto_increment bastaría con crear el indice en ese campo. Pero en este caso, mi campo se incrementa automáticamente. ¿Cuál sería la diferencia de crear en este campo el índice al ser un campo auto_increment o en qué afectaría?

Gracias de antemano por tu respuesta y el enlace.

Saludos.

[pragone]

Pues si es auto_increment... creo que forzosamente debe ser también índice primario.
1.- Podrías colocar el CREATE de la tabla? (usa SHOW CREATE tabla)
2.- En el query que pusiste dice id = id... será id = $id... no?
3.- Es posible que este no sea el query que te de problemas... podrías indicar qué otros queries tipo tienes?

[[DARK_RULO]]

Iniciado por pragone

Pues si es auto_increment... creo que forzosamente debe ser también índice primario.
1.- Podrías colocar el CREATE de la tabla? (usa SHOW CREATE tabla)
2.- En el query que pusiste dice id = id... será id = $id... no?
3.- Es posible que este no sea el query que te de problemas... podrías indicar qué otros queries tipo tienes?

1.- Éste es...

Código:

CREATE TABLE `galerias` (
  `id` int(11) NOT NULL auto_increment,
  `categoria` varchar(20) NOT NULL default '',
  `titulo` varchar(255) NOT NULL default '',
  `thumbnail` varchar(255) NOT NULL default '',
  `carpeta` varchar(100) NOT NULL default '',
  `activa` mediumint(1) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=154 DEFAULT CHARSET=latin1;

2.- Así es. Disculpa. Error de dedo.

3.- La mayoría son queries de este tipo. Sólo en algunos utilizo AND o cosas por el estilo. Ejemplo:

Código:

SELECT * FROM tabla WHERE id = $id AND autor = $usuario

Muchas gracias.

Saludos.

[Carxl]

Hola [DARK_RULO], cómo vas??

Pues mira... si la mayoría de tus consultas son como las de tu post, no deberían molestarte los recursos del server.

Ahora bien, el tema de indexación es solo uno de los tantos pasos que se tienen que tener en cuenta para tener una DB debidamente optimizada.

El diseño de la misma, las consultas que ejecutas, el tipo de tabla y datos que manejas, la cantidad de campos por tablas... Todos ellos y mas son factores para determinar una DB como óptima...

Aclarándote un poco el tema de indexación, pues es la simple regla de la integridad referencial cuando se tratan de DB relacionales, que para cada valor de llave foránea debe existir un valor de llave primaria concordante. Aquí mas

La idea sería que mostraras mas consultas complejas que utilizas para ver si puede ser eso.

Saludos, espero te sirva

[[DARK_RULO]]

Código PHP:

  <?php 
mysql_select_db($database, $grav); 
$query_ultimo_review = "SELECT * FROM reviews WHERE aprobada=1 ORDER BY id DESC LIMIT 0,1"; 
$ultimo_review = mysql_query($query_ultimo_review, $grav) or die(mysql_error()); 
$row_ultimo_review = mysql_fetch_assoc($ultimo_review); 
?>   

Bueno, ese es uno de los distintos querys que tengo en mi sitio. Ese query es únicamente para mostrar el último articulo añadido a la base de datos.

En este otro query, lo que hago es sacar las direcciones de los blogs de los usuarios para que se muestren al azar. Para ello, agrego rand() al query. Supongo que esto podría estar afectando severamente el desempeño.

Código PHP:

  <?php 
// Blog al azar... 
$query_blogs = "SELECT * FROM blogs ORDER BY rand() LIMIT 0,1"; 
$blogs = mysql_query($query_blogs, $gravedanime) or die(mysql_error()); 
$row_blogs = mysql_fetch_assoc($blogs); 
?>   

Esos son los querys más "extravagantes" que tengo dentro de la programación.

Gracias nuevamente por sus respuestas.

Saludos.

[Carxl]

Hola [DARK_RULO], de cuantos registros hablamos??

[[DARK_RULO]]

Poco más de 1000 registros. La base de datos no pesa ni siquiera 1mb (el archivo sql sin "zipear" ni nada de eso).

SALU-2.

[Carxl]

Hola de nuevo [DARK_RULO], pues... lo único que queda por mirar es el diseño de tu DB... hasta ahora nada de lo que me has dicho muestra un deficiencia del tamaño del que habla tu proveedor.

Te sugeriría que hablaras con ellos y que te demuestren con datos cuantificables lo que está pasando, osea que te demuestren que es tu DB la del problema.

Sin embargo, si quieres pásame el .sql de tu db, no hay necesidad de los datos, solo la estructura

Saludos

[[DARK_RULO]]

Bueno, en realidad yo tampoco creo que sea eso. Me faltó mencionar que también tengo instalado un phpbb3 en mi sitio web. Tiene algo así como 20,000 usuarios y unos 50 y tantos mil mensajes. Imagino que esto es lo que está afectando severamente el desempeño. Creo que al final tendré que mudarme a un VPS, para "expandir" recursos. Jeje.

He aquí una pequeña parte del log de lo que sucedía antes de la suspención que me mando soporte técnico.

Código:

| 13337 | usuario    | localhost | base_de_datos_foro | Query   | 93   | Opening tables       | UPDATE phpbb_sessions SET session_time = 1200079562, session_page = 'viewtopic.php?f=34&t=4266&_f_=3 |
| 13342 | usuario    | localhost | base_de_datos_foro | Query   | 1342 | closing tables       | UPDATE phpbb_sessions SET session_time = 1200079566, session_page = 'viewtopic.php?f=10&t=3953&_f_=1 |
| 13343 | usuario    | localhost | base_de_datos_portal | Query   | 1299 | closing tables       | SELECT * FROM blogs ORDER BY rand() DESC LIMIT 0,1

Saludos y muchísimas gracias por su ayuda.

[pragone]

Hola [DARK_RULO]

Primero que nada, el EXPLAIN de MySQL es siempre tu mejor ayuda a la hora de optimizar queries.

Dicho esto. Un SQL como:

Código HTML:

SELECT * FROM blogs ORDER BY rand() LIMIT 0,1

es un mata-mysql :)

Haciendo un explain sobre una base de datos mía con ese query... mira lo que da:

Código HTML:

+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | category | ALL  | NULL          | NULL | NULL    | NULL |  124 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+

Si un query te dice "using filesort"... malo malo.

En vez de aleatorio hazlo rotativo. Así te puedes deshacer del rand.

[BrujoNic]

[DARK_RULO], por favor NO coloques código de programación en esta sección.
Función de la sección de Base de Datos

[[DARK_RULO]]

Ok. Listo y anotádo. No volverá a suceder.

Y muchísimas gracias a pragone y Carxl. Sus conejos me han servido de mucho.

Gracias.

Saludos.

[Carxl]

No hay de que man!!!

[pragone]

Iniciado por [DARK_RULO]

... Sus conejos me han servido de mucho.

Hey!!!

Devuélveme mi conejo!!!! Espero que no te lo hayas comido!

... a la orden