This query kills the whole page (90% of the request time):
/**
* Checks if a conversation exists containing these users (at least two!)
* //TODO: fixme! SUPER-SLOW! 5s on a 6s page load total
*
* @param array $users Users to check on
* @param int $limit Limit - needs at least 2 users
* @return array Results
*/
public function partOfConversations($users, $limit = 5) {
$options = array(
'conditions' => array('ConversationUser.status <' => ConversationUser::STATUS_REMOVED),
'group' => array('ConversationUser.conversation_id HAVING SUM(CASE WHEN ConversationUser.`user_id` in (\'' . implode('\', \'', $users) . '\') THEN 1 ELSE 0 END) = ' . count($users) . ''),
'contain' => array('Conversation' => array('LastMessage')),
'limit' => $limit,
'order' => array('Conversation.last_message_id' => 'DESC')
);
return $this->ConversationUser->find('all', $options);
}
The resulting query is
SELECT `ConversationUser`.`id`, `ConversationUser`.`conversation_id`,
`ConversationUser`.`user_id`, `ConversationUser`.`status`, `ConversationUser`.`created`,
`Conversation`.`id`, `Conversation`.`user_id`, `Conversation`.`title`,
`Conversation`.`created`, `Conversation`.`last_message_id`, `Conversation`.`count`
FROM `comm_conversation_users` AS `ConversationUser`
LEFT JOIN `comm_conversations` AS `Conversation`
ON (`ConversationUser`.`conversation_id` = `Conversation`.`id`)
WHERE `ConversationUser`.`status` < 7 GROUP BY `ConversationUser`.`conversation_id`
HAVING SUM(CASE WHEN `ConversationUser`.`user_id` in
('2ed23d7c-dcc8-4d3b-8e7b-0fe018b0f9bf', '297e0fcc-8880-4bc7-9b57-0ba418b0f9bf')
THEN 1 ELSE 0 END) = 2
ORDER BY `Conversation`.`last_message_id` DESC
LIMIT 5
What it tries to do is to find out whether in a 1..x conversation two or more users are part of it (passed as $users). Is there any way to speed that up?
Conversation 1:N ConversationUser N:1 User
Records are not too many: 70k Conversation, 130k ConversationUser
The fact that this also uses UUIds instead of normal AIID integers is probably making it worse. But it should still not be 5s.
Besides ensuring your sql tables are optimized via indexes for that query, I think you should add another condition- only those records with acceptable user_id's.
Here is the appropriate WHERE clause
Changing the PHP code to: