Fiz uma View bastante complexa no meu banco de dados MySQL e percebi que algumas consultas são lentas. Minha primeira ideia foi adicionar índices, mas não é possível fazer isso em uma visualização, então estou perdido em como melhorar o desempenho da minha consulta.
Compartilharei aqui minha consulta de visualização e farei o possível para explicá-la, depois compartilharei minha estrutura de banco de dados (reduzida ao que é necessário apenas para a visualização).
CREATE VIEW conversations_search AS (
SELECT a.id, a.organization_id, a.status, a.way, a.agent_id, a.subject, a.snooze_until, a.channel_id, a.source, a.contact_id, a.name, a.email, a.created, a.message, a.category, a.is_personal, a.is_customer, a.last_update,
CONCAT(DATE_FORMAT(a.last_update, '%Y%m%d'), a.is_customer, DATE_FORMAT(a.last_update, '%H%i%S'), RIGHT(LPAD(a.id, 6, '0'), 6)) AS priority_sort,
CONCAT(DATE_FORMAT(a.last_update, '%Y%m%d%H%i%S'), RIGHT(LPAD(a.id, 6, '0'), 6)) AS date_sort,
CONCAT(RIGHT(LPAD(CAST((UTC_TIMESTAMP() - a.last_update) AS UNSIGNED) * 100, 12, '0'), 12), RIGHT(LPAD(a.id, 6, '0'), 6)) AS longest_sort
FROM (
SELECT c.id, c.organization_id, c.status, e.way, c.agent_id, c.subject AS subject, c.snooze_until, c.channel_id, c.source, c.contact_id, cn.name, cn.email, e.created, e.message, e.category, c.is_personal,
IF(pc.customer_id IS NULL, 0, 1) AS is_customer,
(
IFNULL(
(SELECT created FROM messages WHERE conversation_id = c.id AND way = 'IN' ORDER BY created DESC LIMIT 1),
IFNULL(
(SELECT created FROM messages WHERE conversation_id = c.id AND way = 'OUT' ORDER BY created DESC LIMIT 1),
(SELECT created FROM message_drafts WHERE conversation_id = c.id)
)
)
) AS last_update
FROM (
SELECT m.way, m.created, m.message, m.conversation_id, 'MESSAGE' AS category FROM messages m WHERE `status` != 'DRAFT'
UNION
SELECT NULL AS way, n.created AS created, n.message, n.conversation_id, 'NOTE' AS category FROM conversation_notes n WHERE `status` != 'DRAFT'
) AS e LEFT JOIN conversations c ON c.id = e.conversation_id LEFT JOIN contacts cn ON cn.id = c.contact_id
LEFT JOIN processor_customers pc ON pc.contact_id = c.contact_id
) AS a
);
Explicação : Esta visualização carrega algumas colunas relacionadas a tickets, incluindo:
- o ID do bilhete
- o Organization_id ao qual o ticket é afetado
- O status (fechado, aberto, etc.)
- a forma como a última mensagem foi enviada (recebida ou enviada)
- O ID do agente atribuído
- O sujeito
- O tempo de soneca (se houver)
- o ID do canal (como [email protegido] )
- o ID do contato (do e-mail)
- O nome do contato
- O e-mail do contato
- A data de criação do ticket
- o conteúdo da última mensagem
- a categoria (se a última entrada for uma mensagem ou nota)
- Se a mensagem recebida for pessoal (o canal para onde o email foi enviado é pessoal, como [email protegido] , ou aberto a qualquer pessoa da equipe, como suporte@..)
- Se o contato for um cliente
- A última vez que o ticket foi atualizado
- Uma coluna usada para classificar por prioridade
- Uma coluna para classificar por data
- Uma coluna para classificar aguardando mais
Definitivamente, não sou um especialista em banco de dados, então talvez já existam muitas melhorias disponíveis nessa consulta, mas o que percebi é que, na produção, uma consulta simples como:
SELECT COUNT(id) FROM Conversations_search WHERE Organization_id = X; Leva cerca de 2,5 segundos para ser executado. Tentei com alguns IDs diferentes e o resultado está entre 900 e 18.000, mas ainda leva cerca de 2,5 segundos.
Claramente, estão faltando algumas otimizações ...
Aqui está minha estrutura de banco de dados (removi algumas colunas inúteis para simplificar aqui):
CREATE TABLE `contacts` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(250) DEFAULT NULL,
`email` varchar(250) NOT NULL,
`created` datetime NOT NULL,
`organization_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`,`organization_id`),
KEY `organization_id` (`organization_id`),
CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11181 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `conversations` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`subject` varchar(250) DEFAULT NULL,
`status` varchar(15) NOT NULL,
`source` varchar(15) NOT NULL,
`created` datetime(6) NOT NULL,
`snooze_until` datetime DEFAULT NULL,
`channel_id` bigint(20) unsigned DEFAULT NULL,
`organization_id` int(11) NOT NULL,
`contact_id` bigint(20) unsigned NOT NULL,
`agent_id` bigint(20) unsigned DEFAULT NULL,
`is_personal` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `agent_id` (`agent_id`),
KEY `channel_id` (`channel_id`),
KEY `contact_id` (`contact_id`),
KEY `organization_id` (`organization_id`),
CONSTRAINT `conversations_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
CONSTRAINT `conversations_ibfk_2` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`),
CONSTRAINT `conversations_ibfk_3` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
CONSTRAINT `conversations_ibfk_4` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17502 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `messages` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`message` mediumtext DEFAULT NULL,
`way` varchar(3) NOT NULL,
`created` datetime(6) NOT NULL,
`last_update` datetime NOT NULL,
`status` varchar(20) NOT NULL,
`conversation_id` bigint(20) unsigned NOT NULL,
`contact_id` bigint(20) unsigned DEFAULT NULL,
`agent_id` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `agent_id` (`agent_id`),
KEY `contact_id` (`contact_id`),
KEY `conversation_id` (`conversation_id`),
CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
CONSTRAINT `messages_ibfk_3` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27845 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `conversation_notes` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`agent_id` bigint(20) unsigned DEFAULT NULL,
`message` mediumtext DEFAULT NULL,
`status` varchar(20) NOT NULL,
`created` datetime(6) NOT NULL,
`conversation_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `agent_id` (`agent_id`),
KEY `conversation_id` (`conversation_id`),
CONSTRAINT `conversation_notes_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
CONSTRAINT `conversation_notes_ibfk_2` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1452 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `message_drafts` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`content` mediumtext DEFAULT NULL,
`created` datetime(6) NOT NULL,
`last_updated` datetime NOT NULL,
`conversation_id` bigint(20) unsigned NOT NULL,
`agent_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `agent_id` (`agent_id`),
KEY `conversation_id` (`conversation_id`),
CONSTRAINT `message_drafts_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
CONSTRAINT `message_drafts_ibfk_2` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=653 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `agents` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(250) DEFAULT NULL,
`email` varchar(250) NOT NULL,
`organization_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ix_agents_email` (`email`),
UNIQUE KEY `email` (`email`,`organization_id`),
KEY `organization_id` (`organization_id`),
CONSTRAINT `agents_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=679 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `organizations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL,
`slug` varchar(250) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ix_organizations_slug` (`slug`),
) ENGINE=InnoDB AUTO_INCREMENT=647 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Como posso melhorar a consulta acima? (A visualização contém esses valores porque posso pesquisar por nome de contato ou e-mail, por status, por agente, por caminho (entrada/saída), por canal, etc. Todas as colunas podem ser usadas na pesquisa.
Obrigado pela ajuda!
