CakePHP, ИМЕЮЩИЙ СУММУ Запрос сверхслабый - где есть место для улучшений?

Question

Этот запрос убивает всю страницу (90% времени запроса):

/**
 * 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);
}

Результирующий запрос

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

Он пытается выяснить, участвуют ли в разговоре 1..x два или более пользователей (передаются как $users). Есть ли способ ускорить это?

Диалог 1:N ConversationUser N:1 Пользователь

Записи не слишком много: 70k Conversation, 130k ConversationUser

Тот факт, что здесь также используются UUIds вместо обычных целых чисел AIID, вероятно, усугубляет ситуацию. Но это все равно не должно быть 5 с.

Answer 1

Помимо обеспечения оптимизации ваших таблиц sql с помощью индексов для этого запроса, я думаю, вам следует добавить еще одно условие - только те записи с приемлемыми идентификаторами user_id.

Вот соответствующий пункт ГДЕ

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

Изменение кода PHP на:

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);
}