Truco MySQL si tienes muchos usuarios

PiradoIV #1 Sep '10 Brongster

Buenas,

Imaginaros que queremos mostrar los últimos 10 usuarios que se han registrado en nuestra web.

SELECT user.name
FROM (user)
ORDER BY user.id DESC
LIMIT 10

Es una buena costumbre tener en la estructura de tus bases de datos los siguientes fields:

created_at (TIMESTAMP) al que le asignaremos NOW() cada vez que le metamos un registro
updated_at (TIMESTAMP) con "on update CURRENT_TIMESTAMP"

Además, al campo created_at le añadimos un índice (acordaros de optimizar), así podremos hacer este tipo de consultas que, en bases de datos grandes, se nota:

SELECT user.name
FROM (user)
WHERE user.created_at >= NOW() - INTERVAL 1 MONTH
ORDER BY user.id DESC
LIMIT 10

¡Profit! =)

PD: Según la velocidad con la que obtengáis nuevos registros de usuarios, se puede cambiar el intervalo por 1 WEEK, 1 DAY, etc. Si lo afináis bien, podéis reducir sin problemas el tiempo de la consulta en más de 45 veces.

SiCk #2 Sep '10

Buen truco, yo siempre me creo campo "modificado" con current_timestamp, pero me parece mejor idea tener los dos por si acaso.
Thx!

J

Janee #3 Sep '10

Buen aporte, gracias.

NeB1 #4 Sep '10

siempre creaba los campos last_modified y created pero nunca le había dado este uso, mola

Beavis #5 Sep '10

Pondría la mano en el fuego por que la optimización que propones es bastante más lenta que la consulta original. Si creas el índice como sugieres en la columna fecha estás haciendo la selección where con un index y el orden sin nada: mysql sólo puede usar un index por cada subconsulta. De este modo para hacer la ordenación, a pesar de que tengas un index individual definido en la columna id (primary por ej) va a tener que usar un algoritmo de ordenación sobre el set de resultados obtenido (si hicieses un explain de esa consulta lo verías reflejado como "Using filesort", mal llamado porque de hecho el algoritmo que usa si mal no recuerdo es quicksort), lo cual es muchísimo mas lento que ordenar usando un índice.

Para optimizar tu consulta y que el order no use filesort deberías tener un index compuesto de las dos columnas (created_at,id). De todos modos puede que otro tipo de consultas se beneficien de acotar más los resultados antes del order y el limit, pero creo que este no es el caso y la consulta original seguiría siendo más rápida (siempre que id esté indexado, claro). Tienes benchmarks o has observado que te mejore el rendimiento? De todos modos si la tabla no tiene muchas entradas o el servidor mucha carga las diferencias van a ser imperceptibles.

Perdona por el rollazo pero una de mis obsesiones es el rendimiento de las consultas, el optimizador de queries del mysql y el comando explain y este tipo de cosas me resultan bastante curiosas, he visto hacer al optimizador de queries cosas muy raras :o_o:

Soltrac #6 Sep '10 MV Wizard

Creo que es como dice #5. Los índices en los SELECT y evitarlos en los WHERE.

PiradoIV #7 Sep '10 Brongster

Le echaré un ojo Beavis, pero vamos, lo probé en una DB en vivo y pasó de tardar 0.6 a 0.003

PiradoIV #8 Sep '10 Brongster

Ahí va eso:

Consulta sin delimitación

Explain

Consulta con delimitación

Explain

Vamos, entiendo que es más rápido ordenar 58 resultados que 1615, ¿hay forma de que lo haga más rápido sin delimitar y sin usar el filesort?

Beavis #9 Sep '10

El problema que tienes es que en la primera consulta, si te fijas en el explain está usando el index active, que es muy muy poco restrictivo (entiendo que es un campo boolean) y entonces te devuelve un resultset enorme que luego tiene que ordenar con filesort, para optimizar eso puedes añadir un index de tres columnas (active,available_search,id) de este modo puede usar la primera parte del index para el where y la segunda para el order by.

A la hora de hacer benchmarks te recomiendo usar SQL_NO_CACHE en la consulta, de este modo no se distorsionan los resultados si la ejecutas varias veces seguidas y entra en juego la caché (ej: SELECT SQL_NO_CACHE user.id ...)

PiradoIV #10 Sep '10 Brongster

Qué crack Beavis, desconocía los índices de más de un field, me abres un mundo xD

Tiempo de ejecución: 0.0005

Ninja-edit: Por las dudas, lo probé también con la delimitación y tarda 0.0007, más lento.

eXtreM3 #11 Sep '10

Joder, cómo sabeis estas cosas!? Mira que llevo años usando mysql y había hecho índices para optimizar, pero esto?? Como dice Pirado, mundo nuevo abierto

NeB1 #12 Sep '10

Podríais pasar por ahí recursos para estudiar principios de optimización para mysql

(para la plebe y eso)

Beavis #13 Sep '10

Como dicen por ahí las limitaciones fomentan la creatividad . Yo fui aprendiendo por mi cuenta a base de errores cuando Mediavida (tanto apache como mysql) estaba alojada junto a otras páginas en un Pentium 4 con 1GB de ram (y de esto no hace tantos años XD). Dediqué tiempo a investigar como optimizar las consultas, tablas (aka pasar de la normalización) y las variables del myisam, analizar logs de consultas lentas para identificar los cuellos de botella, usar el comando explain con las consultas conflictivas (que es una de las herramientas más útiles para esto), cambiar cosas y ver como afectaban los cambios al rendimiento.
Tener una base de datos con tablas que tienen millones de registros (ocupa más de 5GB ahora mismo) que usan más de mil personas simultáneamente es una suerte para probar determinadas cosas e ir aprendiendo de los errores.

En cuanto a recursos os recomiendo por un lado la página http://www.mysqlperformanceblog.com/ y por otro lado High Performance MySQL, ójala hubiera tenido ese libro hace 7 años xD

LOc0 #14 Sep '10

Beavis, creo que hablo en nombre de todos cuando digo:

spoiler

Salu2

eXtreM3 #15 Sep '10

Gracias #13, a ver si en los ratos libres puedo echarle un ojillo ^^

Beavis #16 Sep '10

#14 Hacer un buscador atacando al mysql directamente ni se me pasa por la cabeza, con el tamaño de la tabla de posts (más de 8 millones de registros) no es una opción escalable y tendría un impacto brutal en el rendimiento del foro. Tengo pensado desde hace años implementar el buscador con sphinx pero hay bastantes cosas pendientes en la página que tienen más prioridad y el tiempo que le puedo dedicar es limitado. De momento con el buscador de google se funciona bastante bien. Y no nos desviemos del hilo

NeB1 #17 Sep '10

#9 lo acabo de probar en una consulta con la que tenía mogollón de problemas

de 0,90

a 0,06

eXtreM3 #18 Sep '10

#16 y qué soluciones podrían ofrecerse si hubiera sido en el caso contrario? Es decir, dices que ahora no se puede (debe) hacer un buscador que consulte directamente la tabla post porque son 8 millones de registros y disminuiría el rendimiento del foro. Si el buscador hubiera estado hecho desde el principio de la web logicamente esto no pasaría, y a medida que va creciendo la tabla post vas notando esa disminución en el rendimiento... qué podría hacerse en ese caso? Cambiar el buscador por ese que has puesto? (que ni siquiera he pinchado, no sé lo que es)

NeB1 #19 Sep '10

http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

aquí dejo una pequeña guía de optimización, apta para novatos.

He aprendido algunas cosas de las que no tenía ni idea. Sabíais lo de LOAD DATA??

LOc0 #20 Sep '10

#16
Perdona el Off-topic, era una pequeña coña ya que no es común verte por estos lares. Lo que comentas es completamente lógico. Ojalá algún día se pueda mejorar, porque me haría ilusión leer las paridas que posteaba allá por 2002 xD

Y en cuanto al tema, tomo nota de todos los consejos que estáis poniendo ya que de BBDD no he profundizado más allá de las dos asignaturas de la carrera. Claro que en informática no se puede ser experto de todo por mucho que se empeñen ciertas "empresas" ¬ ¬...

Salu2

Usuarios habituales