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.
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.