Optimizar consulta SELECT MySql

[reprogramator]

Hola amigos:
Tengo dos tablas:
mensajes: mensajeID,fromID,cuerpo,date
destinatarios: mensajeID,ToID

Al enviar un mensaje, se guarda en la tabla mensajes, y además se guarda un registro para cada destinatario en la tabla destinatarios. De esta manera puedo ver una lista de mensajes que he enviado y he recibido con esta consulta:

Código MySQL:

SELECT * 
FROM `mensajes` 
WHERE fromID='".$_SESSION['MyID']."' 
    OR mensajeID IN(SELECT mensajeID FROM `destinatarios` WHERE ToID=".$_SESSION[MyID]." )
ORDER BY datetime DESC 
LIMIT 200
 

Hay alguna manera de optimizar esta consulta con join u otro método?
Sobre todo estoy buscando bajar la carga del servidor ya que, sobre todo la tabla destinatarios puede aumentar su tamaño considerablemente (incluso a dos millones de registros).

Muchas gracias!

[gnzsoloyo]

Por lo pronto, el uso de ORDER BY y GROUP BY es por definición ineficiente en cualquier DMS, sea o no MySQL.
Pero además hay dos cosas: si haces una subconsulta sobre el mismo campo que ya identifica el mensaje, referido a otra tabla, se infiere que hay una relación entre ambas (especialmente si una se llama "mensajes" y la otra "destinatiarios".
En ese caso deberías estar usado un JOIN y no una subconsulta.
Si a esto le agregamos que todo mensaje debería tener obligatoriamente un destinatario, entonces la consulta si o si debería ser INNER JOIN.
Así pues, por empezar la consulta debería andar por acá:

Código MySQL:

SELECT *
FROM `mensajes` M INNER JOIN `destinatarios` D ON M.mensajeID = D.mensajeID
WHERE 
    M.fromID='".$_SESSION['MyID']."'
    AND D.ToID=".$_SESSION[MyID]." 
ORDER BY datetime DESC
LIMIT 200
 

Respecto al ordenamiento, el único modo de mejorarlo es creando un indice sobre el campo o campos invocados en el ORDER BY.

Hay, sí, un error severo: NO debes utilizar nombres como "datetime" para las columnas, porque son palabras reservadas. Las palabras reservadas pueden generar errores sintácticos indetectables, desde el momento en que son parte del SQL (en ese cso es un tipo de dato).

Otra cosa que se infiere es que podría existir un error de diseño de la base, que también conspira contra la performance, desde que si existe un mensaje, el destinatario debe ser un atributo del mensaje (el destinatario es del mensaje emitido, como también el remitente), y el destinatario debería ser un usuario, o un contacto del usuario.
En ese contexto hay dos tres posibilidades:
1) Si es un usuario del sistema, tanto el ID del destinatario como del remitente apuntan a la misma tabla (2 FK distintas a la misma tabla), pero requiere una tabla Mensajes_Destinatarios con dos campos (es tabla relacional).
2) Es un contacto no perteneciente al sistema sino al usuario. Requiere de una tabla de Contactos, una tabla Mensajes_Destinatarios y otra de Mensajes_Contactos.

Tengo la impresión de que a tu sistema le esta faltando algo... o no quedan clara las relaciones, y eso afectará la performance de todas las consultas.

[reprogramator]

Muchas gracias por responder.

Referente a tu comentario "Otra cosa que se infiere es que podría existir un error de diseño de la base, que también conspira contra la performance, desde que si existe un mensaje, el destinatario debe ser un atributo del mensaje (el destinatario es del mensaje emitido, como también el remitente), y el destinatario debería ser un usuario, o un contacto del usuario."

No he llegado a comprender muy bien lo que me quieres decir, pero el tema es que en un principio pensé dejar la tabla mensajes con un campo llamado destinatarios en el cual los destinatarios estarían separado por comas. Y eso (en otra ocasión) ya me lo quitaste de la cabeza.
Ahora no vi otra posibilidad de tener dos tablas para no repetir un registro por cada destinatario en la tabla mensajes. En este caso cada destinatario es el ID del usuario. Y creo que es así, que cada ID de usuario destinatario es un atributo del mensaje.

Muchas gracias por tu ayuda.