MySQL Messaging System Slow Query

Question

Hey guys,

I'm trying to create an inbox messaging system using two tables, one called Message_Topic, and Message. Message_Topic contains all topics created, and the Message table contains the message conversation within each topic.

I'm stuck at the selection of all Topics.

My Query is as follows:

`SELECT umt.intTopicId FROM User uo, User u, User_Message_Topic umt

WHERE umt.intCreatorId = uo.intUserId AND umt.intUserToId = u.intUserId AND ( ( umt.intCreatorId = '1' AND umt.blnReplied ) OR umt.intUserToId = '1' ) AND blnInboxHide = false

ORDER BY dtmLastSent DESC LIMIT 0, 10`

Basically, I need to select all messages that were either sent to the user "intUserToId" which is me OR sent BY ME and that have been replied to.

This query takes 0.85s to run which is way too slow.

Any clues on how I could resolve this?

Thanks in advance.

Armin

Answer 1

SELECT umt.intTopicId FROM User_Message_Topic umt
LEFT JOIN User uo ON umt.intCreatorId = uo.intUserId
LEFT JOIN User u ON umt.intUserToId = u.intUserId
WHERE
((umt.intCreatorId = '1' AND umt.blnReplied) OR umt.intUserToId = 1) AND
blnInboxHide = false
ORDER BY dtmLastSent DESC LIMIT 0,10

I don't know if that'll be really fast, but give it a shot.