consulta más optimizada

[dieguicho]

Estimados, cual de las dos consultas está mejor (si las dos estan mal me dicen)

lo estoy ejecutando sobre una base de datos de 20.000 registros y me da resultados intercalados, pero bastante parecidos...

ELECT users.username, MAX(photos.id) as id FROM users
INNER JOIN photos ON photos.user_id = users.id AND photos.status = 0
WHERE users.status = 0
GROUP BY users.id
ORDER BY photos.id DESC
LIMIT 0,6;

--------------------

SELECT users.username, MAX(photos.id) as id
FROM users, photos
WHERE users.id = photos.user_id AND users.status = 0 AND photos.status = 0
GROUP BY users.id
ORDER BY photos.id DESC
LIMIT 0,6;

gracias!!!

[quimfv]

Pues ni una ni la otra (del todo), la que se acerca más es la primera

Código:

SELECT users.username, MAX(photos.id) as id 
       FROM users INNER JOIN photos 
              ON photos.user_id = users.id 
WHERE users.status = 0 AND photos.status = 0
GROUP BY users.id
ORDER BY MAX(photos.id) DESC
LIMIT 0,6;

Eso da los ultimos 6 usuarios que han entrado alguna foto y el id de la ultima foto que ha entrado cada uno (por orden descendente del id de la ultima foto de cada uno).

Quim

[dieguicho]

Quim,
gracias por tu respuesta!
las dos consultas que pegué hacen lo mismo que la tuya, funcionan perfecto
solo quiero optimizarlas al maximo.

veo que pusiste ORDER BY MAX(photos.id) DESC,
eso es mas veloz que poner ORDER BY photos.id DESC ??

usar JOIN es mas rapido que la otra forma siempre?

[dieguicho]

ah... y me tiró error la consulta
el mysql query browser dice que...

Invalid use of group function

[gnzsoloyo]

No se pueden usar funciones agregadas en ORDER BY ni en GROUP BY. Deben referirse a campos reales o provenientes de una subconsulta.

[dieguicho]

entonces ORDER BY photos.id DESC seria lo correcto no?
pero ahi estaria ordenando por id (cualquiera de la tabla) o por los que ya encontro con MAX(photos.id) ?

[gnzsoloyo]

Analicemos bien el caso:
Este código:

Código sql:

SELECT users.username, MAX(photos.id) AS id 
       FROM users INNER JOIN photos 
              ON photos.user_id = users.id 
WHERE users.STATUS = 0 AND photos.STATUS = 0
GROUP BY users.id
ORDER BY MAX(photos.id) DESC
LIMIT 0,6;
 

Esto te devuelve todos registros de usuarios y el ID de la foto de cada uno que sea el mayor para cada usuario.id. Si lo que deseas es que esas photo.id se ordenen, no lo puedes hacer en la misma sentencia, porque debes convertir la columna devuelta por la consulta en una columna real y no en el resultado de una función agregada. Eso se logra poniendo el resultado de esta consulta dentro de otra, como subconsulta:

Código sql:

SELECT username, id
FROM (
          SELECT users.username, MAX(photos.id) AS id 
          FROM users INNER JOIN photos 
              ON photos.user_id = users.id 
          WHERE users.STATUS = 0 AND photos.STATUS = 0
          GROUP BY users.id ) Tabla1
ORDER BY id DESC
LIMIT 0,6;
 

De esta forma, la consulta se transforma en una tabla en memoria, y de esa tabla se toma el resultado de la consulta principal. Como la consulta principal opera sobre la tabla de memoria, ya toma datos prefiltrados y es muchísimo más rápida.

Pruébalo.

[dieguicho]

buenisimo, la subconsulta se guarda en memoria..
la verdad corre rapidisimo! grande gnzsoloyo!

[quimfv]

A lo dicho por gnzsoloyo absolutamente de acuerdo!!! Lo siento fue un eceso de confianza lo de poner el max(id) en el order by... interprete que lo querrias ordenado por el id maximo y por eso lo puse pero es un error.

En cuanto a si join es mas rapido yo creo que si. Pero no todos los motores són iguales.... de lo que estoy seguro es de que es la forma correcta si tenemos en cuenta los manuales de SQL en mayusculas. Pero cada motor puede tener sus variaciones, yo siempre intento trabajar con el SQL mas estandar posible.

Por otro lado parece logico aqui

WHERE users.id = photos.user_id AND users.status = 0 AND photos.status = 0

se mezclan las restriciones con la llave de vinculación entre tablas mientras que aqui

ON photos.user_id = users.id
WHERE users.status = 0 AND photos.status = 0

el motor sabe de antemano que es cada cosa.


Quim

[dieguicho]

osea que users.status = 0 AND photos.status = 0 son restricciones
pense que para el motor era lo mismo que users.id = photos.user_id

por otro lado.. los INDEX solo funcionan si estan en el WHERE...
si uso JOINS no los estaria aprovechando... no?