[kabuto178]

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.

[andy_pleasants]

Hi

Just to be clear, you want to return the last 20 messages in a conversation between 2 users?

If so you're just missing a few SQL constructs, I think this should work:

SELECT *
FROM member_messages
WHERE
(
(
message_from = userid
AND
message_to = friendid
)
OR
(
message_from = friendid
AND
message_to = usetid
)
)
AND message_I'd <= upperid
ORDER BY message_I'd DESC
LIMIT 20

This will gather messages from user A to user B or user B to user A, where the message is is less than the specified upperid, order them by the message is (highest and therefore latest first) and then take only the first (or because of the sorting latest) 20.

Also if you're trying to implement some sort of paging here the LIMIT construct can take 2 parameters, when you pass 2 parameters the first is how many rows to skip and the second is how many to take so LIMIT 0 20 will skip 0 and take 20 (I.e. the first 20)

This post has been edited by andy_pleasants: 02 March 2013 - 08:47 AM

[andy_pleasants]

Hi again, I've just found this article which is related to your question.

It basically uses the method you were attempting, and which I now believe to be a better method (contrary to my last comment), especially for high usage, multi user systems.

[andrewsw]

AND message_I'd <= upperid
ORDER BY message_I'd DESC

The apostrophes shouldn't be present in the above snippet.

Auto-increment IDS (and similar) should not be relied upon to establish order. These are arbitrary numbers over which we have no control. Your timestamp field is a much better candidate for ordering, as andy_pleasants 's link discusses.

[andy_pleasants]

Quote

The apostrophes shouldn't be present in the above snippet.

correct, I was doing that on my tablet, and didn't notice it had auto-corrected to that.

[kabuto178]

Thanks for your input checking the link as well, sorry for the late reply.