Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created a messaging system with MySQL structure is as follows

      `message_id` int(11) NOT NULL AUTO_INCREMENT,
      `message_from` int(11) NOT NULL,
      `message_to` int(11) NOT NULL,
      `message_body` varchar(255) NOT NULL,
      `message_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `message_deleted` tinyint(1) NOT NULL DEFAULT '0',
      `message_read` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`message_id`),
      KEY `message_from` (`message_from`),
      KEY `message_to` (`message_to`)

Currently The PK is an AUTO_INCREMENT so I am wondering if using this kind of key will be efficient for this type of system. Also the main reason why i am having a asking this question is to help in my function I have been having some issues with loading more messages. due to the fact that messages are deleted the PKs will not be consistent such as 1,2,3,10,255,300. When I try to run the more function using

(SELECT MAX(message_id)
 FROM member_messages 
 WHERE (message_to = :userId
 AND message_from = :friendId)
 OR  
 (message_to = :friendId
 AND message_from = :userId))AS max_id,
FROM member_messages AS MSG
WHERE (MSG.message_to = :userId
AND MSG.message_from = :friendId)
OR 
(MSG.message_to = :friendId
AND MSG.message_from = :userId)
AND MSG.message_deleted = '0'
HAVING MSG.message_id >= max_id - 20
AND MSG.message_id <= :upperId 
ORDER BY message_id ASC

In effect this query should return messages from the last 20 entered, but due to the PK being inconsistent sometimes no messages are returned. Eg. if the max_id is 200, 200- 20 is 180 and the messages with ID between this range are deleted or not associated with this members(userId) recipient(friendId). Any insight into this problem as to whether I should change the key type or change the query itself? Thanks for your time.

EDIT

Just a side note I used ASC because I wanted the newest messages to appear at the bottom of the DIV, while the fetched results would be older and appear at the top.

share|improve this question
    
I'm not sure what the problem is: You're querying for data and you are aware that the data that fits your constraints might have been deleted by the time you run the query. So: Change your query or don't delete your data. Also: comparing message_time to :upperId looks wrong because it suggests comparing a date to an ID (probably numeric?), but maybe this does make sense in your system... –  FrustratedWithFormsDesigner Feb 28 '13 at 19:29
    
That was a mistake, i corrected it –  kabuto178 Mar 1 '13 at 4:11
add comment

1 Answer

up vote 2 down vote accepted

Your primary key is fine. I don't see a reason to change it.

The issue you are having is because you are basing your query on the messsage_id and it being without gaps. You could base your queries on the message_time column (I suppose this holds the datetime that the message was sent).

Even if you keep using the messsage_id for the queries (assuming that biggest id means the latest message - which may not always be correct), you can still write a query that returns always 20 messages, gaps or not - just change ORDER BY message_time DESC to ORDER BY messsage_id DESC in the 3 places it appears:

SELECT *
FROM
  (   ( SELECT message_id, message_time,
               'U <- F' AS direction
               message_read, message_body
        FROM member_messages AS msg
        WHERE message_to = :userId
          AND message_from = :friendId
     --   AND message_deleted = 0
        ORDER BY message_time DESC
            LIMIT 20 
      )
    UNION ALL
      ( SELECT message_id, message_time,
               'U -> F' AS direction
               message_read, message_body
        FROM member_messages AS msg
        WHERE message_to = :friendId
          AND message_from = :userId
          AND message_deleted = 0
        ORDER BY message_time DESC
            LIMIT 20 
      )
    ORDER BY message_time DESC
        LIMIT 20 
  ) AS tmp 
ORDER BY message_time ASC;

Other issues:

You probably meant to have the deleted = 0 check on both cases (message sent by me to friend and sent by friend to me). If yes, undelete the commented line above - and note that AND has higher precedence than OR in SQL. The condition you probably wanted to use was (note the removed pair of parenthesis):

WHERE (    MSG.message_to = :userId
       AND MSG.message_from = :friendId
      OR 
           MSG.message_to = :friendId
       AND MSG.message_from = :userId
      )
  AND ...

So, you could write the query like this:

SELECT *
FROM member_messages AS msg
WHERE (    MSG.message_to = :userId
       AND MSG.message_from = :friendId
      OR 
           MSG.message_to = :friendId
       AND MSG.message_from = :userId
      )
      AND message_deleted = 0
ORDER BY message_time DESC
    LIMIT 20 ;

I used UNION for efficiency reasons. Complicated conditions with OR may not be optimized very well. You could also add an index on (deleted, message_from, message_to, message_time).

share|improve this answer
    
I will try some tinkering in a bit, but I think you understand my concept, getting all the messages in one view sent from me to you and from you to me. If UNION will be more efficient then I think that is the route I need to take also I was curious about using an index on deleted and message_time, how would i be using that index? Just a side note I used ASC because I wanted the newest messages to appear at the bottom of the DIV, while the fetched results would be older and appear at the top. –  kabuto178 Feb 28 '13 at 20:19
    
I'm not sure I understand what you asking about the index. The above 4-column index would be more than enough - for this particular query - and probably the best. Of course, adding an index for every query you have, will have you soon with too many indexes - and that is not usually good (for INSERT/UPDATE/DELETE efficiency). Efficient indexing requires you evaluate all the queries/statements you need running and making some balance between excessive and no indexing. –  ypercube Feb 28 '13 at 20:23
    
What is the purpose of 'U <- F' AS direction in your first query sample? –  kabuto178 Feb 28 '13 at 23:37
1  
'incoming' (User <- Friend) or 'outgoing' (User -> Friend) message. –  ypercube Feb 28 '13 at 23:40
    
One more question, because I want to understand the concept and get what I was doing wrong. How will I get messages in batches of 20, at first I want to get the newest 20 messages then the 20 messages before that and so on etc.. –  kabuto178 Mar 1 '13 at 0:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.