CakePHP HAVING SUM Query superslow - where is room for improvement?

Question

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.

can you give more details about your table structure on the database? like what indexes do you have for the tables used in this query? — gurel_kaynak, Nov 15 '14 at 09:29
Adding to @gurel_kaynak request, you should run that query through another tool (e.g., commandline) preceded with the term `EXPLAIN`. That will show the indexes used. IMO: You need an index on comm_conversation_users.status. — AgRizzo, Nov 15 '14 at 13:51
Indexes are Conversation: id (primary), user_id - ConversationUser: id (primary), user_id, conversation_id. Adding an index for status didnt help. But that field is already tinyint(2) anyway. — mark, Nov 15 '14 at 15:04

score 1 · Accepted Answer · answered Nov 15 '14 at 13:59

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

WHERE `ConversationUser`.`status` < 7 
  AND `ConversationUser`.`user_id` in 
    ('2ed23d7c-dcc8-4d3b-8e7b-0fe018b0f9bf', '297e0fcc-8880-4bc7-9b57-0ba418b0f9bf')

Changing the PHP code to:

public function partOfConversations($users, $limit = 5) {
    $options = array(
        'conditions' => array('ConversationUser.status <' => ConversationUser::STATUS_REMOVED
                              ,'ConversationUser.user_id' => $users),
        '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);
}

Awesome! The thing is, in another similar query I seemed to have that additional clause. I don't know why I didnt have it anymore for this one. But it now runs superfast. Thanks a lot! — mark, Nov 15 '14 at 15:17

CakePHP HAVING SUM Query superslow - where is room for improvement?

1 Answers1