作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
此查询会杀死整个页面(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秒。
最佳答案
除了确保您的 sql 表通过该查询的索引进行优化之外,我认为您应该添加另一个条件 - 仅那些具有可接受的 user_id 的记录。
这是适当的 WHERE 子句
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);
}
关于mysql - CakePHP HAVING SUM 查询超慢 - 哪里有改进的空间?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26942751/
我是一名优秀的程序员,十分优秀!